View Single Post
  #7 (permalink)  
Old January 11th, 2005, 12:11 PM
SerranoG SerranoG is offline
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

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