RE: Compare/Hide Dupliactes based on one field

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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;

"Doctor" wrote:

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!


.

Relevant Pages

  • RE: Compare/Hide Dupliactes based on one field
    ... Some clubs have more than one director. ... would like my query to find all directors, but if it finds two directors from ... I have these fields in the query which contains duplicates (Please ... Everything I have looked at before, will either find duplicates where the ...
    (microsoft.public.access.queries)
  • Compare/Hide Dupliactes based on one field
    ... I have a query that finds all directors of all clubs in the USA and includes ... Some clubs have more than one director. ... Everything I have looked at before, will either find duplicates where the ...
    (microsoft.public.access.queries)
  • Duplicate query
    ... I don't care which Cust Material it picks up as long as ... query it will not do this, either with the find duplicates. ... Juan ...
    (microsoft.public.access.queries)
  • Re: Delete duplicates from a table
    ... If you don't care which record gets saved, ... table with a compound primary key that will prevent duplicates from being ... Then create an append query that copies data from the old table to ... Roger ...
    (microsoft.public.access.queries)
  • Re: Deleting duplicate records/same table
    ... append the tblUniqueRecords fields into the fields on tblMaster. ... The new table without the duplicates is called ... Prepared By is the type of query I was ...
    (microsoft.public.access.queries)