Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 9th, 2005, 10:08 PM
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote
  #2 (permalink)  
Old January 9th, 2005, 11:36 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Can you post your table structure?


Jaime E. Maccou
Reply With Quote
  #3 (permalink)  
Old January 10th, 2005, 12:35 AM
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

8403213D PTE John Mother 93213343

Reply With Quote
  #4 (permalink)  
Old January 10th, 2005, 11:15 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #5 (permalink)  
Old January 10th, 2005, 04:25 PM
Authorized User
 
Join Date: Jul 2004
Location: clapton-in-gordano, n.somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Reply With Quote
  #6 (permalink)  
Old January 10th, 2005, 08:49 PM
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #7 (permalink)  
Old January 11th, 2005, 12:11 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #8 (permalink)  
Old January 12th, 2005, 01:04 AM
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old January 12th, 2005, 01:54 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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!


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #10 (permalink)  
Old January 13th, 2005, 09:31 PM
Registered User
 
Join Date: Jan 2005
Location: Singapore, , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Group Within another Group, xslt1.0 jhansib4u BOOK: XSLT Programmer's Reference, 2nd Edition 4 November 22nd, 2007 01:24 AM
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Distinct Eddywardo SQL Server 2005 1 September 2nd, 2006 07:41 AM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
Group by , Sub Group by and Sum mateenmohd SQL Server 2000 1 March 29th, 2005 09:51 AM



All times are GMT -4. The time now is 09:55 PM.


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