Delete Query Criteria

[DontTell]

Hi -

I am trying to delete duplicate records from a single table. The table has multilple records of duplicate purchase orders. In these instances I need to delete those records that have the same $amt but different qty.
I have already created the delete query but am unsure how to specify the criteria which should be based on the $amt and qty.

Simply said, I need the code to remove all Duplicates with the same $ but different qty.

How help would be greatly appreciated.

[Stewart Ross]

Hi, and Welcome to Bytes!

This is not a trivial problem. Deleting records is a very, very final thing to do, so be sure you know all the implications before you do so.

The most obvious thing to ask is to what value is the quantity different? How do you know which record has the correct quantity you want to keep, and which ones have incorrect quantities you want to delete?

If you have, say, three records for purchase order 1000 - which you should not have if you designed the application correctly, but you tell us you do have duplicates - and these records have quantities 10, 100 and 200 listed; which one, if any, is correct, and how would you know??

I have my doubts about the source of your question - real world purchase order systems are rarely, if ever, so poorly designed, which makes me think this is a homework question - but in any event until you come up with a means of answering the questions I've posed above I'd suggest you don't delete anything at present...

-Stewart