Select maximum price for each duplicate set of records in "Find Duplicates Query"

[Ghoztrider]

I've created a find duplicates query with "part number" and "price" fields. Part Number being the duplicate value field. I need to be able to find the highest price for each set of duplicate records and delete the rest. Example......

Part#: Price:
Widget1 $25.00
Widget1 $32.00


Widget1 $15.00

I need to keep Widget1 for $32.00 and delete the rest. Any direction would be appreciated. Thank you.

[Dal Jeanis]

Okay, lots of possibilities here, depending on the rest of the record. Here's one.

What I'd suggest is that you do this in a test version of your database -

(1) create a new table by copying the old table, structure only. Let's assume that it had a layout something like this

OldTable / NewTable layout
    PART#    Number
    Field1   Text
    Field2   Text
    Price    Currency
    Field3   Number
    Field4   Text
    Field5   Memo

(2) Now you make an append query with a group by. Use Max() to get your price, use (First) for everything else.

INSERT INTO NewTable
  SELECT 
    PART#,
    First(Field1), 
    First(Field2), 
    MAX(Price),
    First(Field3), 
    First(Field4), 
    First(Field5) 
FROM 
   OldTable 
GROUP BY
   PART#;

3) Now check to make sure that the resulting table is reasonable. Especially check memo fields to make sure they haven't been truncated.

4) If that worked, then you can back up the old table in your live database, delete all the data from it and then copy your new table into it.