------->>> Please help on filtering out duplicate records.

[isabel2005]

Hi there,

I'd like to filter out so that the name only appear once base on LastOfDate.

Sample data in query

Customer_____Date__________Product
John_________02/02/2008_____A
John_________05/03/2007_____B
John_________03/06/2006_____A
Nick_________04/04/2006_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A

So in the query, I just want to have John, Nick and James appear once with the LastOfDate.

The result should be
John_________02/02/2008_____A
Nick_________04/04/2007_____B
James_______05/04/2008______A

I included the db file which only perform LastOfDate and unique name, however since John has product A and B, it won't get rid of B.

SELECT DISTINCT query1.Name, Last(query1.Date) AS LastOfDate, query1.Product
FROM query1
GROUP BY query1.Name, query1.Product;

I just want one Name, LastOfDate and one product, regardless of A or B.

Thanks,
Isabel

 

[Jon K]

You can first build a Totals Query to Group By name and Max date.
Then in a second query, join the first query back to the table to retrieve the fields.

qryOne:
SELECT sample.Name, Max(sample.Date) AS MaxOfDate
FROM sample
GROUP BY sample.Name;

qryTwo:
SELECT sample.ID, sample.Name, sample.Date, sample.Product
FROM sample INNER JOIN qryOne ON (sample.Date = qryOne.MaxOfDate) AND (sample.Name = qryOne.Name);

Run the second query.


Note: Though you can achieve the same in one query using subqueries, using an Inner Join in the above two-query approach is more efficient.
.