I have a table similar to this, sorted 1st by field 1 and then by field2:

field1 field2
A 3
A 2
A 1
B 2
C 6
C 4
C 1


I would like to go thru & remove all duplicate field1 entries except the one w the largest field2 value:

field1 field2
A 3
B 2
C 6


I had been doing this in xl by sorting 1st by colA then colB (hi to low) and then simply deleting duplicate rows starting at the end with the following script:

Sub delDupRows()
LastRow = ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 2 Step -1
If Range("B" & r).Value = Range("B" & r).Offset(-1, 0).Value Then
Range("B" & r).EntireRow.Delete
End If
Next r
End Sub


I'm sure there is a more elegant way to do this in Access, possibly by selecting all field1 values w the largest field2 and then saving as another table. Any help would be appreciated.

Thanks
D

MDS,

Use a couple of queries:


Get pairs of Field1 and Max(Date)

Query1:


Select Field1, Max(field2)
From Yourtable
Group By Field1


Pair that query up with your real table, deleting the ones that are not
the max dates.

Query2:


Delete A.*
From YourTable As A Left Join Query1 As B on
A.Field1 = B.field1 And
A.Field2 = B.Field2
Where B.Field2 Is Null


At least I thing that's what you want.

Wayne

Thanks for the reply Wayne...not having any luck though. I included a zip file w both Access 2007 & 2002/2003 with a simplified ver of the table.

1. Small item I forgot to mention: if 2 items are identical in Text1 and Number1 I wanted to take the one w the most recent Date1...there are 2 Text1(B), Number1(7) entries but they have 2 diff Date1 values

2. Any way to do a (nested) Select on TestTable to do this all in 1 query?

Thanks again
Dana

Sprog,

I took a look at this, and I don't think you can do it in one query, as Wayne has already pointed out. However, you can store one short query as your base, and then simply execute a delete query from those stored findings. Maybe use this as your stored query for the base of the extracted table data (query named testQRY for example):SELECT testtable.text1, max(testtable.number1) AS number1
FROM testtable
GROUP BY testtable.text1;Then, use this as your DELETE query, which will be the only one that you will ever have to run:DELETE * FROM TestTable
WHERE testtable.date1 NOT IN (SELECT testtable.date1
FROM testtable
WHERE testtable.text1 IN (SELECT testQRY.text1 FROM testQRY) AND
testtable.number1 IN (SELECT testQRY.number1 FROM testQRY) AND
testtable.date1 = dmax("[date1]", "testtable",
"[text1] = '" & [text1] & "' AND [number1] = " & [number1]));

So, here's the latest...

- Adam's solution (testQRY, testQRY2):
Worked, HOWEVER, took 30 min to delete 774 of 780 rows in the test DB...for the real DB, the query will have to delete ~2.975 million of ~3 million rows, and more will be added quarterly.

- Wayne's solution (Query1, Query2):
"Could not delete from specified tables"


Seems like there must be a simpler way. I attached test2.zip with the test table and all 4 queries.

Thanks again for your help.
Dana

Anybody...?

How about:


SELECT field1, Max(field2) as Field2 INTO New_table_name
FROM Table
GROUP BY field1

Missed your bit about the date. That can probably be handled with something like:


SELECT query.field1, query.field2, Max(table.date)
FROM Query INNER JOIN ON (query.field1 = table.field1) AND (query.field2 = table.field2)
GROUP BY query.field1, query.field2

chergh

Thanks for the reply...using the following I got "Syntax error in FROM clause."


SELECT Query1.Text1, Query1.Number1, Max(testtable.Date1)
FROM Query1 INNER JOIN ON (Query1.Text1 = testtable.Text1) AND (Query1.Number1 = testtable.Number1)
GROUP BY Query1.Text1, Query1.Number1;




I got a reply on another forum that I was able to get to work and wanted to post it in case it helps anyone else:


SELECT t1.Text1, t1.Number1, MAX(t1.Date1) AS maxDate
FROM testtable t1
INNER JOIN
(SELECT Text1, MAX(Number1) AS maxNumber
FROM testtable
GROUP BY Text1) AS t2
ON t1.Text1 = t2.Text1 AND t1.Number1= t2.maxNumber
GROUP BY t1.Text1, t1.Number1;


Thanks to everyone!