Cant get rid of Duplicates in query

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Hi,
Can anyone help advise me on how I can avoid seeing
duplicates in the following:
I shall be sending a report of information to various
customers depending on some simple criteria but I want to
exclude information where there is duplicate information
in three fields of the table (none of them are PKs).
Obviously if you have the 'unique values' property set to
Yes that means 'exclude information where values are
unique for ALL the fields in the query' whereas I'm
looking at three out of 8 or more fields. I did a simple
pre-query that returned unique values in the three fields
but if I then use that as the basis for the main query
there are still duplicates showing. Below is a brief
description of what I've done:

Query A based on Table A =
Unique values for Field1 AND Field2 AND Field3
(ie duplicates are allowed in one or more fields but not
all three)

Query B based on Query A =
Each field in Query A joined to the equivalent field in
Table A (ie all records in Query A take precedence)

As you can tell, I don't really know what I'm doing! Can
you explain the solution in layman's terms?!

Many thanks in advance.
Lee
 
You might TRY a totals query where you group by the values you want to be unique
and then use FIRST (or LAST) on the other fields. This won't be updatable, but
if you are doing a report that should not be a problem.

SELECT Field1, Field2, Field3,
First(Field4) as Fld4, First(Field5) as Fld5,
First(Field6) as Fld6, First(Field7) as Fld7,
First(Field8) as Fld8
FROM TABLENAME
GROUP BY Field1, Field2, Field3
 
Back
Top