Unique Records in a Query (Access 2002)

Topic

New Reply

Another question
I have a database I’m trying to set up with a query (3 tables) that will give me unique names. The Select statement below of course shows duplicates as a lot of people can be on more than 1 committee but we only want to send them 1 e-mail. I read all the current posts on No Duplicates etc and none of them had 3 tables. Can anyone give me some suggestions. Thanks again. This is a great forum. Robin

SELECT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;

Reply | Quote

Replies

Change your select from:
SELECT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;
to
SELECT DISTINCT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;

I don’t know what the fields ATA_Committee.Lcomm & Committee.TabComm are, these may not give you distinct values, if not then delete these 2 fields from the query.

HTH
Pat

Reply | Quote

Hi Pat – no they aren’t unique – they give the name of the committees that the people belong to (which can be more than one). Thank you very much that works for me.
How are things in Melbourne today (or tomorrow) for me.
Robin

Reply | Quote

Hi Robin

The weather is overcast and drizzly, not too good for being outside, how about your side of town.

Pat

Reply | Quote

About the same here. Temp is 7 cel. Have you ever been to Canada. I thought Melbourne was always very warm. This is your summer isn’t it?

Pat I posted that other problem with a table that has gotten very large – any solutions that you can think of?
Robin

Reply | Quote

7 cel is not too bad as long as there is no wind.

Well it’s still our Spring, the temp is about 15 cel. No Melbourne gets very cold in the Winter months (and other times) when the wind blows off the Southern Ocean, then it gets bitterly cold (what they don’t tell you here is the real temp after the Wind Chill factor is taken into account).

I did visit Toronto, Ontario (or is it the other way around) quite a while back. It was delightful, it was nowhere near long enough.

I have already responded to that post.
Pat

Reply | Quote