p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Distinct or Group By? (http://p2p.wrox.com/showthread.php?t=23657)

sprion January 9th, 2005 10:08 PM

Distinct or Group By?
 
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


jemacc January 9th, 2005 11:36 PM

Can you post your table structure?


Jaime E. Maccou

sprion January 10th, 2005 12:35 AM

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

8403213D PTE John Mother 93213343


SerranoG January 10th, 2005 11:15 AM

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

elansolutionsltd January 10th, 2005 04:25 PM

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


sprion January 10th, 2005 08:49 PM

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!! :)



SerranoG January 11th, 2005 12:11 PM

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

sprion January 12th, 2005 01:04 AM

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 !!


SerranoG January 12th, 2005 01:54 PM

Quote:

quote:Originally posted by sprionActually though, I'm hoping to see some SQL statements instead
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.

Quote:

quote:Also, I think I need a random NOK of Jack's to be used, rather than the TOTAL NOKs he has.
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! [:p]


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

sprion January 13th, 2005 09:31 PM

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



All times are GMT -4. The time now is 10:45 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.