Compare/Hide Dupliactes based on one field

[Doctor]

I have a query that finds all directors of all clubs in the USA and includes
all of their mailing information. Some clubs have more than one director. I
would like my query to find all directors, but if it finds two directors from
the same club, I would like to only select one of them (I don't care which
one).

I have these fields in the query which contains duplicates (Please
understand that the records will be different except for the one field ClubID)

ContactID, FullName, ClubID, Address, CSZ, Country, ContactZip

Everything I have looked at before, will either find duplicates where the
entire record is the same, or only show records that are not duplicated. I
tried to do an unmatched query, but you must choose two tables, and it
doesn't let you choose the same table for comparison.

Any help would be tremendously appreciated!

 

[BobT]

Since you don't care which director shows up, use the MAX function on that
field and just use a basic join. Assuming you are joining on the CLUDID
field and each director has its own ContactID:

SELECT Max(Table2.ContactID) AS MaxOfContactID, FullName, ClubID, Address,
CSZ, Country, ContactZip
FROM Table2 INNER JOIN Table1 ON Table2.CludID = Table1.CludCode
GROUP BY Table2.ClubID;

 

[John Spencer]

One method would be a totals query like the one below

SELECT First(SomeTable.ContactID) as ContactID
, First(SomeTable.FullName) as FullName
, First(SomeTable.ClubID) as ClubID
, First(SomeTable.Address) as Address
, First(SomeTable.CSZ) as CSZ
, First(SomeTable.Country) as Country
, First(SomeTable.ContactZip) as ContactZip
FROM SomeTable
WHERE Country = "USA"
GROUP BY ClubID



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Doctor]

Thanks for the thought, I tried your idea, but couldn't get it to work. Where
did table 1 and table 2 come from. I wanted to only work with one table, and
I didn't see where you would use what I might call T1 and T2 or making two
instances of the same table??

 

[Doctor]

Thanks so much! That did the trick. I didn't know about the first and last in
the drop down. I'll be able to use this is several places!

 

[John Spencer]

Caveat: First and Last return the first or last record accessed in the
group. So they are in most cases essentially random about which record
is returned.

The good thing is that all the fields are from the same record when you
use first or last.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================