8403213D PTE John Mother 9123456
8403213D PTE John Father 9321323
8403213D PTE John Sister 9111111
8221234Z MSG Dick Spouse 8321345
8323157G BG Tom Daughter 9236579

These are the current results I'm getting..

How should I query to get this instead? ::

8403213D PTE John Mother 9123456
8221234Z MSG Dick Spouse 8321345
8323157G BG Tom Daughter 9236579

Thanks :P

Can you post your table structure?


Jaime E. Maccou

SocialID Rank Name [NOK Relationship] [NOK Contact No.]

8403213D PTE John Mother 93213343

You've listed what you're getting and what you want. You have not told us what you're starting out with and what criteria you're looking for. At first glance, it looks like you're only interested in female relations for NAME.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Without knowing the table you are putting the query on, I seems to me that you simply want the First of the [SocialID] Field. Rather than choosing GroupBy select First instead. This does mean that you MUST sort your underlying table by SocialID.

Alan T

ahh .. i get it .. these is wat i should provide :X

[personnel table]
[SocialID] [Rank] [Name]
8403213D PTE John
8401234A MSG Dick
8404321B BG Tom
8405555F CPL Harry

[Next of Kins table]
[personal_sid] [nokRelationship] [nokContact]
8403123D Mother 91234567
8403123D Father 95435425
8403123D Sister 97856867
8401234A Spouse 92348432
8404321B Daughter 95436546

Basically, I neeed my results to show ALL personnel, even though he does not have a NOK. (a simple join will not show CPL HARRY as he has no NOK entered)

Also, to keep my final results count correct, I need PTE John to appear just once and not 3 times since he has 3 NOKs entered.

MY BAD results:
8403213D PTE John Mother 91234567
8403213D PTE John Father 95435425
8403213D PTE John Sister 97856867
8401234A MSG Dick Spouse 92348432
8404321B BG Tom Daughter 95436546

MY WANTED results:
8403213D PTE John Mother 91234567
8401234A MSG Dick Spouse 92348432
8404321B BG Tom Daughter 95436546
8405555F CPL Harry


thanks for all the help!! :)

Oh, OK. I assume you have a query in design view with two tables (Personnel and Next of Kin) joined by SocialID and Personal_sid.

Double-click on the join line between the two tables. A "Join Properites" dialog box will appear. The default choice is the first radio button. You want the second or third button (depending on the order you put the two tables in your query). That's why Harry is not showing up on the query results. Choose the button that says, "Include All Records from 'Personnel' and only those records from 'Next of Kin' where the joined fields are equal." Then click OK.

Next, double-click anywhere around the tables, but not on the join line. A "Query Properties" dialogue box will open. Make sure "Unique Values" is set to YES. Close the box.

Next, in your output fields below, the reason John is coming out three times is because you have nokRelationship there and John has three people. Click the summation button on the toolbar; or on the menu, click VIEW > TOTALS. Under the nokRelationship column, choose COUNT from the dropdown (the default is GROUP BY). Delete the nokContact column.

Now you’ll get what you want, but instead of relationships, you’ll get the NUMBER of relationships that person has, including Jack’s three and Harry’s zero.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Very nice SerranoG, I haven't tried it but I assume it should work.

Actually though, I'm hoping to see some SQL statements instead :)

Also, I think I need a random NOK of Jack's to be used, rather than the TOTAL NOKs he has. THanks again !!

If you open the query in design view, you can click on VIEW and choose SQL. That will show you the SQL language for it.

If you show the relationship rather than the count if it, you'll end up getting three Jacks again. That may be desirable in a game of Poker, but not here!


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

haha .. ok how about the FIRST NOK listed for Jack only .. instead of 3 ?
ANy ideas?