Help with a query

[EAB1977]

Hi guys. I have many duplicate records in a database and I need to get
the record with the highest date and not show the rest of the duplicate
records. I have about 100,000 records. My fields are:

ProductCode
MoldID
PlantName
LastUpdate

For example if I have 2 records like:

ProoductCode MoldID PlantName LastUpdate
1 1 A 5/11/2005
1 1 B 7/8/2005

I need to show the second one, and not the first.

Here is my query now.

SELECT [Find duplicates for Mold0EPS].ProductCode, [Find duplicates for
Mold0EPS].MoldID, [Find duplicates for Mold0EPS].PlantName, [Find
duplicates for Mold0EPS].LastUpdate
FROM [Find duplicates for Mold0EPS]

Please help!!!

 

[Marshall Barton]

Hi guys. I have many duplicate records in a database and I need to get
the record with the highest date and not show the rest of the duplicate
records. I have about 100,000 records. My fields are:

ProductCode
MoldID
PlantName
LastUpdate

For example if I have 2 records like:

ProoductCode MoldID PlantName LastUpdate
1 1 A 5/11/2005
1 1 B 7/8/2005

I need to show the second one, and not the first.

Here is my query now.

SELECT [Find duplicates for Mold0EPS].ProductCode, [Find duplicates for
Mold0EPS].MoldID, [Find duplicates for Mold0EPS].PlantName, [Find
duplicates for Mold0EPS].LastUpdate
FROM [Find duplicates for Mold0EPS]

SELECT D.ProductCode, D.MoldID, D.PlantName, D.LastUpdate
FROM [Find duplicates for Mold0EPS] As D
WHERE D.LastUpdate =
(SELECT Max(X.LastUpdate)
FROM [Find duplicates for Mold0EPS] As X
WHERE X.ProductCode = D.ProductCode)

 

[Guest]

Use two queries. Change to use your table and query names from Table28 and
Query76.
SELECT Table28.ProoductCode, Max(Table28.LastUpdate) AS MaxOfLastUpdate
FROM Table28
GROUP BY Table28.ProoductCode;

SELECT Table28.ProoductCode, Table28.MoldID, Table28.PlantName,
Table28.LastUpdate
FROM Table28 INNER JOIN Query76 ON (Table28.LastUpdate =
Query76.MaxOfLastUpdate) AND (Table28.ProoductCode = Query76.ProoductCode);