Duplicate records in query - How to show only one each

[Wytze]

Hi,

I'm having trouble with duplicate records in a query. The purpose of
the query is to generate a mailing list for my newsletter.

I have a table Contacts, out of which I draw a query of those who
receive my newsletter. I have another table of Groups of which my
contacts may or may not be a member.

Some Contacts are a member of more than one Group, so their records
appear double or triple in the query. Therefore, they end up receiving
multiple copies of my newsletter (and get angry about it).

I've tried several remedies involving Totals in the query design
(Count, First, etc). None work.

The problem seems to be in the link between the two tables. For
usability's sake, I can't force referential integrity on the join
between them. But I do have to set the query to contain all records in
Contacts and only those from Groups that have a link to a Contact
(otherwise the number of contacts in the query plummets).

So only a number of my contacts actually have a join, but some of them
have several. It seems that the query doesn't get its concept of a
unique records from the Contacts table; instead it considers any
contact OR any Contact-Group-join as a unique record. Hence it won't
amalgamate the multiple contacts into one row when I try the Totals
approach.

What can I do to fix this?

With kind regards,

Wytze Schouten

 

[Michel Walsh]

Use DISTINCT or a total query with a GROUP BY on each field (that must be
unique, and use an appropriate aggregate on other fields, such as LAST).


Hoping it may help,
Vanderghast, Access MVP

 

[RealGomer]

THANK YOU Mr WALSH! Yes, I know I'm shouting. I did a search for this very
purpose and all of the results required far too much jumping and jiving. You
obviously have to use Access as an enduser, not just a programmer.