Remove duplicate entries based on lowest number in a given field

[gojets1721]

I've attached an example database with a similar table to what I'm working with. I'm looking to create a query that removes all duplicate customer entries based upon whichever entry has the lowest number in the DocumentAge field. For example, for the Emily Smith customer, the query would only include the entry where the DocumentAge is 12 and not include the entries with 344 and 91.

Any ideas?

 

[jdraw]

Try

SELECT tblDocuments.CustomerID
, Min(tblDocuments.DocumentAge) AS MinOfDocumentAge
FROM tblDocuments
GROUP BY tblDocuments.CustomerID;

 

[arnelgp]

you mean only Retain emily's record where documentAge is 12 (the lowest).

DELETE *
FROM tblDocuments
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM tblDocuments AS Dupe
WHERE (Dupe.CustomerID = tblDocuments.CustomerID) AND (Dupe.DocumentAge < tblDocuments.DocumentAge));

 

[gojets1721]

Try

SELECT tblDocuments.CustomerID
, Min(tblDocuments.DocumentAge) AS MinOfDocumentAge
FROM tblDocuments
GROUP BY tblDocuments.CustomerID;

what would be the sql if I wanted to include the customer first name, and last name? Whenever I try to include those, it seems to mess up the query

 

[jdraw]

Call the code given previously Query1.

Create a new query (Query2) that uses your table and query1.

Query2 sql is:

SELECT tblDocuments.ID
, tblDocuments.CustomerID
, tblDocuments.CustomerFirstName
, tblDocuments.CustomerLastName
, tblDocuments.DocumentAge
FROM tblDocuments INNER JOIN Query1 ON
(tblDocuments.DocumentAge = Query1.MinOfDocumentAge) AND
(tblDocuments.CustomerID = Query1.CustomerId);

 

[gojets1721]

Call the code given previously Query1.

Create a new query (Query2) that uses your table and query1.

Query2 sql is:

SELECT tblDocuments.ID
, tblDocuments.CustomerID
, tblDocuments.CustomerFirstName
, tblDocuments.CustomerLastName
, tblDocuments.DocumentAge
FROM tblDocuments INNER JOIN Query1 ON
(tblDocuments.DocumentAge = Query1.MinOfDocumentAge) AND
(tblDocuments.CustomerID = Query1.CustomerId);

This worked great!! Thank you

 

[jdraw]

You're welcome. Happy to help.

 

[arnelgp]

I'm looking to create a query that removes all duplicate customer entries

i was thinking you need Delete query.
anyway, you can do it in just 1 query:

select tblDocuments.*
FROM tblDocuments
WHERE DocumentAge = (SELECT Min(DocumentAge) FROM tblDocuments AS T WHERE T.CustomerID = tblDocuments.CustomerID);