469,921 Members | 2,198 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,921 developers. It's quick & easy.

Deleting Duplicate Values in a MS Access Query

Here's my problem in exact replication:

I have used the find duplicate query in Access, and the query
determined the following duplicate values by the following query:
In (SELECT [DocNo] FROM [File Name] As Tmp GROUP BY [DocNo] HAVING
Count(*)>1 )
DocNo Amounts
ABC 12
ABC 123
BDC 10
BDC 1000
Now I want to go further by saying if the DocNo is duplicate as shown
in the query above then to delete the least of the two amounts
associated with it.
Note: the Amounts are in a separate column and can be filtered in
ascending order or the lesser of the two amounts comes first.
Please help if you know a better query that can delete the duplicate
DocNo and the lesser of the two amounts that are associated with it.
I appreciate any help you can offfer on this topic.

May 24 '06 #1
3 4957
When you use the Wizard to build a Duplicates query, it uses the
keyword first. I wonder if there is a keyword such as Min that could be
applied to the Amounts column. You could then save these values using a
Make Table query, delete all where there are dupes, and then append
back from the new table. Just an idea. I'm no expert.

-Tony M.

May 24 '06 #2
Thanks Tony, just tried something along the min value but didn't work
in connection with the first duplicate value.

May 24 '06 #3
If you have the luxury of deleting all records and re-appending from a work
table you could skip the Find Duplicates operation altogether.

Create a select query based on your table and drag all the columns into it.
Change it to a Grouping query. On the Amounts column change Group By to
Maximum, so that you get the maximum value found. If you have some unique
identifier for each row, such as an autonumber field, change Group By to
Minimum. (Or Maximum, it doesn't matter for this purpose. You can't do
this if the unique identifier is used as a foreign key in some other table,
but I'm guessing that's not the case in this situation.

Now make the Grouping query a Make Table query. Create the work table,
which will contain only the maximum amount for each DocNo. If there was
only one amount, that's what you get, if there were 10 amounts, you get the
biggest one.

Delete all rows in the original table. Now build an append query based on
the temporary table and append the records (containing only the maximum
value for each DocNo) back to the orignal table.

May 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Nathan Bloom | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.