Delete specific duplicte records

  • Thread starter Thread starter kswa098
  • Start date Start date
K

kswa098

I have a table with some duplicate records. Data is added every day, and
sometimes more than one data point is added in one day.

Now I need find every place in my table where more than one value exists for
a day and delete all but the highest value for that day. The result needs to
be a table with only one record per day, and that one record needs to
contain the highest value recorded for each day.

I figured out how to delete dups, but I can't glean from the help files how
to process duplicates and delete specific ones. Can anybody help?
 
think i know, although slightly long winded

got to queries and choose new button at the top
choose find duplicate query
choose only the relevant table
choose only the DATE field and the VALUE field (the one you only want to
keep the highest of)
finish the query and go into design view
your date and value will be the first 2 on the left
for both, change first to max
run the query when you have some test data in the table
should come back with highest value for each date

now you have a query that will select the right records, you can append the
records to another table, delete the rest of the original table, then move
them back (probably all in one macro)

you might have to add the rest of the fields from the table into the
duplicates query as hidden so you can append all the data and not lose any.

HTH
 
Hi,


Make a backup. It is important to make tests on data that does not matter.


DELETE mytable.* FROM mytable
WHERE fieldToBeMaxed <>
DMAX("fieldToBeMaxed", "mytable",
"dateField=" & Format( dateField,
"\#mm-dd-yyyy\#" ) )




The DMAX find the maximum value, for a given date. The where clause effect
would be that a record will be deleted records, if for this date, its value
does not match the maximum so reported.


Hoping it may help,
Vanderghast, Access MVP
 
DMAX! I knew there was something I wasn't thinking of. Problem sovled,
thanks so much.
 
I used another solution, but this is a good trick. Thanks much for your help
(and it wasn't long winded, it was perfectly clear!)
 
Back
Top