Delete Query based on results in a seperate table

[kshope25]

I need to delete info in tblshipmentdata_working that matches 2 fields (Plant and Code) in LITOSD.

for example, I have in LITOSD a row with Plant = 203 and Code = PD1.
When I run the delete query, it's pulling Plant = 201 and Code = PD1 from tblshipmentdata_working to delete. It looks like it's totally ignoring my code to match on both plant and code.

DELETE*
FROM tblshipmentdata_working
WHERE (((tblshipmentdata_working.Code) In (SELECT LITOSD.Code FROM LITOSD)) AND ((tblshipmentdata_working.Plant) In (SELECT LITOSD.Plant FROM LITOSD)) AND ((tblshipmentdata_working.Active)=-1));

 

[Beetle]

Unless LITOSD contains only one row, there is nothing in your query that specifies that the values need to be in the same row in LITOSD. All your doing is telling Access to delete all rows from tblshipmentdata_working where those values appear anywhere in LITOSD.

Note: Make a backup before attempting any of the following.

There are a couple of ways you could approach this. You could create a join between the two tables using both fields, but to get this to work you'll need to use the DISTINCTROW keyword. Example;

DELETE DISTINCTROW tblshipmentdata_working.*
FROM tblshipmentdata_working
INNER JOIN LITOSD
ON (tblshipmentdata_working.Code = LITOSD.Code) AND (tblshipmentdata_working.Plant = LITOSD.Plant)
WHERE tblshipmentdata_working.Active=-1;

Or, you could use a WHERE EXISTS clause. Example;

DELETE tblshipmentdata_working.*
FROM tblshipmentdata_working
WHERE EXISTS (Select Code, Plant From LITOSD Where LITOSD.Code = tblshipmentdata_working.Code
And LITOSD.Plant = tblshipmentdata_working.Plant)
And tblshipmentdata_working.Active=-1;

 

[BoyDay]

Another way to do it could be to create a concatenated field in each table combining plant and code, and then match on that.

 

[kshope25]

Sean, that worked great! Thank you so much! I worked on that all day yesterday!
BoyDay, that would have probably worked too now that I think about it.
Thanks for both of your help!