Delete Query Comparing 2 tables (1 Viewer)

NearImpossible · Oct 20, 2019

Wondering if someone can help me out on a query...

I have a RMA form containing a RMA table with a RMAID field, Facility Name, Facility Address and Date.

On my Equipment table I have several fields with one of them being a matching RMAID field.

What I would like to do is whenever I close the RMA form, it compares the RMA table to the Equipment table and deletes the record from the RMA table if it doesn't find a matching ID in the Equipment table.

I am using SQL tables linked to an access DB and would like to do this through a passthrough query

NearImpossible · Oct 20, 2019

I tried the following suggestion off of another post but it deleted everything, even though there was a matching ID in the equipment table.

Code:

DELETE RMAs
FROM RMAs
INNER JOIN FacilityEquipment
ON RMAs.RMAID<>FacilityEquipment.[RMA ID]

This works to delete any records that match, but of course is the opposite of what I want it to do.

Code:

DELETE RMAs FROM RMAs WHERE RMAs.RMAID IN
(SELECT [RMA ID] FROM FacilityEquipment);

NearImpossible · Oct 20, 2019

After a little more searching, I got it to work with the following

Code:

DELETE FROM RMAs WHERE NOT EXISTS (SELECT * FROM FacilityEquipment WHERE RMAs.RMAID = FacilityEquipment.[RMA ID]);

theDBguy · Oct 20, 2019

NearImpossible said:
After a little more searching, I got it to work with the following

Code:

DELETE FROM RMAs WHERE NOT EXISTS (SELECT * FROM FacilityEquipment WHERE RMAs.RMAID = FacilityEquipment.[RMA ID]);

Hi. Good job! Glad to hear you weren't discouraged by a lack of participation today. Cheers!

NearImpossible · Oct 20, 2019

theDBguy said:
Hi. Good job! Glad to hear you weren't discouraged by a lack of participation today. Cheers!

I never get discouraged, I just wasn't finding what I was looking for so thought I would post here as you guys have been a great help on past questions, while I was on the hunt....

theDBguy · Oct 20, 2019

NearImpossible said:
I never get discouraged, I just wasn't finding what I was looking for so thought I would post here as you guys have been a great help on past questions, while I was on the hunt....

Hi. I'm sure if someone saw it, they would have jumped in. I was out for most of the day. We appreciate your patience.

Delete Query Comparing 2 tables (1 Viewer)

NearImpossible

Registered User.

NearImpossible

Registered User.

NearImpossible

Registered User.

theDBguy

I’m here to help

NearImpossible

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread