jan's diary

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.

This morning I had a job interview by phone with a school abroad. I got the interview after two of my referees had sent the school feedback on me. The interview lasted one minute.

- Good morning, Jan.
- Good morning, X.
- Is it true that you were born in 1952?
- Yes, that is correct.
- I am sorry! I just looked at your CV again. We are not allowed to hire anyone aged over 55.
- I am afraid I can’t do anything about that.
- I am sorry. Goodbye.
- Goodbye.

A friend of mine works at a school where all student information is held in several spreadsheets. It is recognised that it would be better to use a database, but one worries about who should maintain it.

My friend has offered his help, not to revolutionise the system, but to help with its evolution. Last week he wrote some VBA Excel code that merged the information on eighteen forms/tutor groups, each on a separate spreadsheet, into one spreadsheet that was then saved as a csv file and imported into MS Access.

The office will continue to maintain the form information in their spreadsheet, but with the click of a button my friend can transfer its data to his MS Access table where queries are run either directly or interested teachers can link to the table from a database where they create their own queries.

Not a revolution, but helping with the evolution.