how not to delete multiple records?

[vb4me]

Hello,

I have written a query that deletes a certain record based on some criteria. It is working great except for one issue.

If say I want to delete 00101 product number. If there is only one occurance of this product number it will be deleted - this is fine, but if there are multiple occurances then all are deleted although I only want to delete one of them.

Is there any thing I can do in a query to stop running the query after 1 deletion or will I have to write some code.

Any assistance would be appreciated.

 

[cmmrfrds]

This is one of the reasons to always put a unique id on a table then you can use the unique id to delete or update by. Add an auto number or time stamp column to your table.

 

[KornGeek]

If there is more than one with the matching part number, how would you determine which one to delete, or would you care?

 

[vb4me]

Korngeek,

I do not care what one it is as long as only one of them go. I suppose I could add an unique id but if I did not have to it would be good.

 

[KornGeek]

I know this is not the most graceful solution, but since nobody else has offered one, here it goes.

You can create a ToBeDeleted table that uses the part number as a primary key. You would then use a series of queries.

Query1 - Delete all data from the ToBeDeleted table
Query2 - Append records with the selected part number to the ToBeDeleted table (It will ignore duplicates of the same part number)
Query3 - Delete all records in the Main table that are in the ToBeDeleted table

Again, this isn't graceful and there's probably a better way, but it should get the job done for now.

Good Luck.