I am working on a school administration program in Visual FoxPro 9. To recognise students sometimes only the nick name is wanted. But what if two students have the same nick name? How does one tell who is who?
This code says who share the same nick name:
select nname, COUNT(id) as x from students WHERE statusid = 1 group by nname HAVING x > 1
This was the result:
| Alex |
3 |
| Anna |
2 |
| Anushka |
2 |
| Ben |
3 |
| Benjamin |
2 |
| David |
2 |
| Grace |
2 |
| Harry |
2 |
| Jack |
2 |
| James |
4 |
| Jan |
2 |
| Jason |
3 |
| John |
2 |
| Kevin |
3 |
| Lily |
2 |
| Mai |
2 |
| Max |
2 |
| Nan |
2 |
| Nancy |
2 |
| Nicky |
2 |
| Nuk |
2 |
| Peter |
3 |
| Philipp |
2 |
| Ploy |
3 |
| Pop |
2 |
| Poppy |
2 |
| Sabrina |
2 |
| Sam |
2 |
| Sarah |
2 |
| Tan |
2 |
| Vanessa |
2 |
| Vishal |
2 |
But how does one only find those who share the same nick name who are in the same tutor group.
SELECT id, nname, formid ;
FROM students as a ;
WHERE (statusid = 1 ;
AND nname in ;
(SELECT nname FROM students WHERE id <> a.id AND formid=a.formid and statusid = 1)) ORDER BY formid
The code worked like a dream:
id nname formid 457 Ben 4 458 Ben 4 297 James 5 523 James 5 194 James 12 200 James 12
There are 2 Bens in the form with id 2, 2 James in 5, and 2 James in 12.
When I extracted the nname I added the first character in the last name of those students who shared last name. For one form group it looked like this.

Luckily enough the first letter of the last name separated the students. But what if it had not? The next step of improvement is to find all who share the same nick name, give them a character from their last name, see who has the same name now, give those another character in the last name, and so on till no one shares the same name.
It should not be too difficult to program. A loop is all that is needed.
If this problem crops up in many situations one could even write a general function for field1, field2, and table1. Lo and behold, someone has probably already written one. 