query challenge (1 Viewer)

[pser]

I have a challenge with a query. I have a table person which are in a table department which is in a company table.
The PK in person is a personal id, the 9. number in the key to tell if you're male or female, i.e. if the number is odd then the person is male and if it's even it's female.

What i want is to create a query which show the number of male and female in each department in each company.
I found out that i must use this if statement:
Iif((mid(personID,9,1) mod 2) <> 0 then something...

How can i implement this in a query? I know how to do it in vb code.

Regards
Pser

 

[mdemarte]

Are you sure it would not be easier to set a field to M or F for male or female?

Try this: Sum(Iif(((mid(person,9,1) mod 2) <> 0,1,0) will give you the number of males in your database. Count(person) will give you the total number of records. Therefor, subtract the first number from the second will give you the females. Or, you could check the first expression for = 0 for the females.

 

[pser]

I've not consider that yet. I thought there might be a way of using the already registered personal identification number which all people have here in Norway. But solution is maybe easier the way you describe.

Pser