Help with a new Query

[Guest]

I have a table with 90K records and for each item named CCN, there can be
multiple records for 39 different offices and 2 different levels of
expertise. Example: AAAA (CCN), 2000XTRT (office), Level 2 (Choice of 2 or
3); AAAA, 2000XTRT, Level 2; AAAA, 2000XTRT, Level 3; AAAA, 2000XTRT, Level
3. I want to pull out for each different CCN, only one Level 2 and one Level
3 record for each different office location. How would I do this?

 

[John Vinson]

I have a table with 90K records and for each item named CCN, there can be
multiple records for 39 different offices and 2 different levels of
expertise. Example: AAAA (CCN), 2000XTRT (office), Level 2 (Choice of 2 or
3); AAAA, 2000XTRT, Level 2; AAAA, 2000XTRT, Level 3; AAAA, 2000XTRT, Level
3. I want to pull out for each different CCN, only one Level 2 and one Level
3 record for each different office location. How would I do this?

You've got this three-table one to many to many relationship all in
ONE TABLE? Ouch! You should REALLY consider normalizing this into at
least three tables.

Does it matter which Level 2 or Level 3 record you select? If not,
create a Totals query; Group By CCN and Office, and use First on the
Totals line under Level 2 and Level 3.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps