Deleting Records In One Table Based on Query Results From Another

[Woodpile]

Hello All,

I am hoping someone can guide me in the right direction. I am trying to delete records in one table based on query results from another table.

I have two tables of interest: For clarity, call them Tbl A and Tbl B. Tbl B is entirely a subset of Tbl A with certain fields included. The common fields between the two are Order, which is a unique identifier, and User Status, which changes over time.

Each week, I update the records in Tbl A. I have written a query that finds the common Orders where the User Status has changed in Tbl A and I want to delete those records in Tbl B.

I have tried to create a Delete Query several different ways but either get an error saying I did not specify the table from which to delete the records or I get an error saying the selected records cannot be deleted. I do not know enough about SQL to know how to edit the code the way the help file from Microsoft is trying to direct me.

Any help would be greatly appreciated.

Thank you for getting this far,

Ed

 

[stumac]

It's not 100% clear how the query for finding your records delete come together. But you can basically use an SQL statement with IN - within the criteria of a query.

Here is an example:

DELETE *
FROM TblB
WHERE [Order] In ([COLOR=#b22222]SELECT TblA.[Order] FROM TblA INNER JOIN TblB ON TblA.[Order] = TblB.[Order] WHERE (TblA.Status)<>[TblB].[Status][/COLOR])[COLOR=#b22222][/COLOR]

You should be able to change the SQL to fit your criteria - only return the Order field within the select query though.

You will see I have placed Order within square brackets - this is a reserved word in Access as is used in SQL of queries to sort records - you should try not to use reserved words where possible.

 

[Woodpile]

Thank you, stumac. I will give this a try later today and report back.

 

[Woodpile]

Sorry for the delay, I got distracted by day-to-day operations and couldn't try this until now.
It did not work and I'm sure it is because I did not explain correctly so here goes a more in-depth attempt.

I have two tables: 01- Yardwide Work Orders Table (Yardwide for brevity) and 07- Planning Priorities Longevity Table (Planning for brevity). The Yardwide Table is updated every week and the Planning Table is updated based on changes to the Yardwide Table. The Planning Table is a subset of the Yardwide Table and contains some fields from the Yardwide Table.

I want to delete records from the Planning Table based on a change in the User Status field in the Yardwide Table. I have a query (named Planner- 41- Out Of Planning) that finds the records to delete by looking at the User Status field in the Yardwide Table and identifying those records in the Planning Table with the same value in the Order field, which is common to both tables and also a unique identifier.

When I try to build the Delete Query, either with the query used to identify the records to delete or with another query using that query, I get the message "Specify the table containing the records you want to delete."

Here is the SQL from the query used to find the records to delete. This works perfectly (line spaces added for clarity):

SELECT [07- Planning Priorities Longevity Table].[Data Date], [07- Planning Priorities Longevity Table].[Name], [07- Planning Priorities Longevity Table].[Revision], [07- Planning Priorities Longevity Table].[Section], [07- Planning Priorities Longevity Table].[Main WorkCtr], [07- Planning Priorities Longevity Table].[Order], [07- Planning Priorities Longevity Table].[Description], [07- Planning Priorities Longevity Table].[User status], [07- Planning Priorities Longevity Table].[Last Change], [01- Yardwide Work Order Table].[User status]

FROM [07- Planning Priorities Longevity Table] INNER JOIN [01- Yardwide Work Order Table] ON [07- Planning Priorities Longevity Table].Order = [01- Yardwide Work Order Table].Order

WHERE ((([01- Yardwide Work Order Table].[User status])="RTSC" Or ([01- Yardwide Work Order Table].[User status])="PLAA" Or ([01- Yardwide Work Order Table].[User status])="WAMA" Or ([01- Yardwide Work Order Table].[User status])="FCOM"));


Here is the SQL from the Delete Query that uses the query above to find the records to delete from the Planning Table, again line spaces added for clarity:

DELETE [07- Planning Priorities Longevity Table].[Data Date], [07- Planning Priorities Longevity Table].Name, [07- Planning Priorities Longevity Table].Revision, [07- Planning Priorities Longevity Table].Section, [07- Planning Priorities Longevity Table].[Main WorkCtr], [07- Planning Priorities Longevity Table].Order, [07- Planning Priorities Longevity Table].Description, [07- Planning Priorities Longevity Table].[User status], [07- Planning Priorities Longevity Table].[Last Change]

FROM [07- Planning Priorities Longevity Table] INNER JOIN [Planner- 41- Out Of Planning] ON [07- Planning Priorities Longevity Table].Order = [Planner- 41- Out Of Planning].Order;


This works fine in View Mode but when I try to run it, I get the before mentioned error about which table to delete from. If I am reading the SQL correctly, the table should be the 07- Planning Priorities Longevity Table in the FROM statement. The database is not Read Only as I also have an Append Query that finds new orders in planning from the Yardwide Table and appends those records into the Planning Table.


I'm hoping there is now enough information that someone can help sort this out as it seems to me very much like stumac's advice but with the actual table and query names I use.

I went with two queried because I could not get just one Delete Query to work and have had success before using one query to find records and another to perform some action, like an Append. This is my first attempt at deleting records other than manually.

 

[stumac]

Ok so looks like your select query is called Planner- 41- Out Of Planning - ?

you should be able to use that as mentioned above:

DELETE FROM [07- Planning Priorities Longevity Table] WHERE [Order] IN (Select [Order] from [Planner- 41- Out Of Planning])

Note you don't need to define what fields you are deleting as a delete query deletes the whole record.

To test of it is the correct records before you start deleting change to a select Query:

SELECT * FROM [07- Planning Priorities Longevity Table] WHERE [Order] IN (Select [Order] from [Planner- 41- Out Of Planning])

 

[Woodpile]

Thank you, stumac. That worked perfectly.