Access Select / Delete SQL statement

[msylvest]

I currently have 2 tables, with different data. Some of the records are
the same, and I would like to remove all the records that exist in both
tables. I have written a SQL statement that identifies these records:

SELECT Mike_Merge_FS.FS_Item, Mike_Merge_FS.FS_Lot,
Mike_Merge_FS.FS_Stk_Room, Mike_Merge_FS.FS_Qty
FROM Mike_Merge_FS, Mike_Merge_VB
WHERE (((Mike_Merge_FS.FS_Item)=[Mike_Merge_VB].[VB_Item]) AND
((Mike_Merge_FS.FS_Lot)=[Mike_Merge_VB].[VB_Lot]) AND
((Mike_Merge_FS.FS_Stk_Room)=[Mike_Merge_VB].[VB_Stk_Room]) AND
((Mike_Merge_FS.FS_Qty)=[Mike_Merge_VB].[VB_Qty]))
ORDER BY Mike_Merge_FS.FS_Item;

That query produces the exact records that I would like removed from my
table. Then, I tried to create a DELETE SQL statement:

DELETE Mike_Merge_Test.*, Mike_Merge_VB.*
FROM Mike_Merge_Test, Mike_Merge_VB
WHERE (((Mike_Merge_Test.FS_Item)=[Mike_Merge_VB].[VB_Item]) AND
((Mike_Merge_Test.FS_Lot)=[Mike_Merge_VB].[VB_Lot]) AND
((Mike_Merge_Test.FS_Stk_Room)=[Mike_Merge_VB].[VB_Stk_Room]) AND
((Mike_Merge_Test.FS_Qty)=[Mike_Merge_VB].[VB_Qty]));

When I run this query, I get the error "Operation must use an
updateable query". Finally, I created a query where I tried to select
the opposite of what I selected in the first statement:

SELECT Mike_Merge_FS.FS_Item, Mike_Merge_FS.FS_Lot,
Mike_Merge_FS.FS_Stk_Room, Mike_Merge_FS.FS_Qty
FROM Mike_Merge_FS, Mike_Merge_VB
WHERE (((Mike_Merge_FS.FS_Item)<>[Mike_Merge_VB].[VB_Item]) OR
((Mike_Merge_FS.FS_Lot)<>[Mike_Merge_VB].[VB_Lot]) OR
((Mike_Merge_FS.FS_Stk_Room)<>[Mike_Merge_VB].[VB_Stk_Room]) OR
((Mike_Merge_FS.FS_Qty)<>[Mike_Merge_VB].[VB_Qty]))
ORDER BY Mike_Merge_FS.FS_Item;


Instead of selecting "X AND Y AND Z", I tried to select "NOT X OR NOT Y
OR NOT Z", the opposite. This query does not work, and causes my
computer to lock up. Can anyone help me with this problem? Let me
know if you need additional information, and I greatly appreciate any
help.

 

[John Spencer (MVP)]

To get an updatable query I would try something like the following SQL.

SELECT Mike_Merge_FS.FS_Item, Mike_Merge_FS.FS_Lot,
Mike_Merge_FS.FS_Stk_Room, Mike_Merge_FS.FS_Qty
FROM Mike_Merge_FS INNER JOIN Mike_Merge_VB
ON (((Mike_Merge_FS.FS_Item)=[Mike_Merge_VB].[VB_Item]) AND
((Mike_Merge_FS.FS_Lot)=[Mike_Merge_VB].[VB_Lot]) AND
((Mike_Merge_FS.FS_Stk_Room)=[Mike_Merge_VB].[VB_Stk_Room]) AND
((Mike_Merge_FS.FS_Qty)=[Mike_Merge_VB].[VB_Qty]))
ORDER BY Mike_Merge_FS.FS_Item;

The problem that I see is that if you delete from one table then you can no
longer identify the records in the other table as being duplicated. The
simplest way to do this would be to add a True/False (YesNo) field to table 2
(DeleteThese) and populate it with an update query. Then delete the duplicates
in table 1 based on the select query and then delete the records in table2 based
on the DeleteThese field. That ends up being three queries that would look
something like:

UPDATE Mike_Merge_FS INNER JOIN Mike_Merge_VB
ON Mike_Merge_FS.FS_Item=[Mike_Merge_VB].[VB_Item] AND
Mike_Merge_FS.FS_Lot=[Mike_Merge_VB].[VB_Lot] AND
Mike_Merge_FS.FS_Stk_Room=[Mike_Merge_VB].[VB_Stk_Room] AND
Mike_Merge_FS.FS_Qty=[Mike_Merge_VB].[VB_Qty]
SET Mike_Merge_VB.DeleteThese = True

DELETE Mike_Merge_FS.*
FROM Mike_Merge_FS INNER JOIN Mike_Merge_VB
ON Mike_Merge_FS.FS_Item=[Mike_Merge_VB].[VB_Item] AND
Mike_Merge_FS.FS_Lot=[Mike_Merge_VB].[VB_Lot] AND
Mike_Merge_FS.FS_Stk_Room=[Mike_Merge_VB].[VB_Stk_Room] AND
Mike_Merge_FS.FS_Qty=[Mike_Merge_VB].[VB_Qty]


DELETE Mike_Merge_VB.*
FROM Mike_Merge_VB
WHERE Mike_Merge_VB.DeleteThese = True

I currently have 2 tables, with different data. Some of the records are
the same, and I would like to remove all the records that exist in both
tables. I have written a SQL statement that identifies these records:

SELECT Mike_Merge_FS.FS_Item, Mike_Merge_FS.FS_Lot,
Mike_Merge_FS.FS_Stk_Room, Mike_Merge_FS.FS_Qty
FROM Mike_Merge_FS, Mike_Merge_VB
WHERE (((Mike_Merge_FS.FS_Item)=[Mike_Merge_VB].[VB_Item]) AND
((Mike_Merge_FS.FS_Lot)=[Mike_Merge_VB].[VB_Lot]) AND
((Mike_Merge_FS.FS_Stk_Room)=[Mike_Merge_VB].[VB_Stk_Room]) AND
((Mike_Merge_FS.FS_Qty)=[Mike_Merge_VB].[VB_Qty]))
ORDER BY Mike_Merge_FS.FS_Item;

That query produces the exact records that I would like removed from my
table. Then, I tried to create a DELETE SQL statement:

DELETE Mike_Merge_Test.*, Mike_Merge_VB.*
FROM Mike_Merge_Test, Mike_Merge_VB
WHERE (((Mike_Merge_Test.FS_Item)=[Mike_Merge_VB].[VB_Item]) AND
((Mike_Merge_Test.FS_Lot)=[Mike_Merge_VB].[VB_Lot]) AND
((Mike_Merge_Test.FS_Stk_Room)=[Mike_Merge_VB].[VB_Stk_Room]) AND
((Mike_Merge_Test.FS_Qty)=[Mike_Merge_VB].[VB_Qty]));

When I run this query, I get the error "Operation must use an
updateable query". Finally, I created a query where I tried to select
the opposite of what I selected in the first statement:

SELECT Mike_Merge_FS.FS_Item, Mike_Merge_FS.FS_Lot,
Mike_Merge_FS.FS_Stk_Room, Mike_Merge_FS.FS_Qty
FROM Mike_Merge_FS, Mike_Merge_VB
WHERE (((Mike_Merge_FS.FS_Item)<>[Mike_Merge_VB].[VB_Item]) OR
((Mike_Merge_FS.FS_Lot)<>[Mike_Merge_VB].[VB_Lot]) OR
((Mike_Merge_FS.FS_Stk_Room)<>[Mike_Merge_VB].[VB_Stk_Room]) OR
((Mike_Merge_FS.FS_Qty)<>[Mike_Merge_VB].[VB_Qty]))
ORDER BY Mike_Merge_FS.FS_Item;

Instead of selecting "X AND Y AND Z", I tried to select "NOT X OR NOT Y
OR NOT Z", the opposite. This query does not work, and causes my
computer to lock up. Can anyone help me with this problem? Let me
know if you need additional information, and I greatly appreciate any
help.