Duplicates in queries from multiples tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that uses 3 linked tables - one which give the addresses of
clients address, one that shows all the people at that address and one that
shows various mailing lists they belong to. I am asking the query to show
all people that belong to two different lists but only show them once if they
are on both lists (some people will be on both lists and some not) eg Jon
Smith needs to get brochure 1 and brochure 2 but Jane Smith only gets
brochure 1 - i only want Jon Smith's name to appear once every though he gets
both brochures. I only want the name once, i dont mind what lists they
appear on but if they are on either list they need to be shown. They have
unique ID numbers so i've tried setting the unique values to yes and this
seems to get rid of some of the duplicates but not all. any suggestions?
thanks
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

It is much easier to visualize and fix the SQL if we have your query.

General advice - Don't display any fields you don't need to display. Use
DISTINCT in the query to limit the values returned. So you may have
something like.

SELECT DISTINCT C.ClientID, C.ClientName, A.Street, A.City, A.State,
A.PostalCode
FROM (CLIENTS as C INNER JOIN LISTS as L
ON C.ClientID = L.ClientID)
INNER JOIN ADDRESSES as A
ON C.AddressID = Address.AddressID
WHERE L.ListName in ("A","B")
 
You can use the GROUP BY operator to restrict the dups.

e.g.
Select PersonID, PersonName from tablename GROUP BY PersonID
 
Thanks for your replies, but I have just found the answer. The Uniqe Values
does work but because we have lists in two different tables it doesn't. If
the lists are all in the same table then it works. I don't know why it was
set up this way but at least i know how to fix it now. Hopefully this may
help anyone else with the same problem! thanks
 
Back
Top