Cant get rid of Duplicates in query

[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

 

[John Spencer (MVP)]

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

 

[Lee]

Hi John,
I see what you mean. I'll try that.
Many thanks,
Lee