Delete Query Not Working because table not specified

[Woodpile]

Hello,

I am trying to write a delete query to remove records from a table based on records in another table. There is a common field and I want to delete records in table 80 that have no matching records in table 01 (Below). The query works just fine but when I switch it to a Delete Query, it will still show me the correct records in View but I get an error stating "Specify the table containing the records you want to delete"

I can't change the Where in any field to From because the application immediately changes it back to Where.

Here is the SQL if that helps.

DELETE [80- Section Backlog Holding Table].Order, [01- Current Backlog Table].Order
FROM [80- Section Backlog Holding Table] LEFT JOIN [01- Current Backlog Table] ON [80- Section Backlog Holding Table].[Order] = [01- Current Backlog Table].[Order]
WHERE ((([80- Section Backlog Holding Table].Order) Like "*") AND (([01- Current Backlog Table].Order) Is Null));

Any help is appreciated. I can't figure out what I'm doing wrong.

 

[xenou]

Probably the first line should have only one table in it:


Instead of:
DELETE [80- Section Backlog Holding Table].Order, [01- Current Backlog Table].Order

try this instead:
DELETE [80- Section Backlog Holding Table].Order


but left joins with deletes are occasionally tricky with MSAccess - don't hold your breath.


Edit:
Note that logically a delete query is for deleting one or more records from a single table. There is never really any reason for a column list in the first line. I think in Access sql commonly you see it like this:
DELETE [80- Section Backlog Holding Table].* WHERE ...

In most other SQL flavors you would only give table name pure and simple:
DELETE [80- Section Backlog Holding Table] WHERE ...

 

[stumac]

AFAIK in SQL both access and other SQL variances you would need a FROM clause. You don't need to specify which fields (or *) in access as with other SQL:

Delete FROM  [80- Section Backlog Holding Table]
where [Order] not in (Select [Order] from [01- Current Backlog Table])

I wouldn't use Order as a field name if you can help it, its a reserved word in access. OrderID or OrderNo would be preferred.

 

[Woodpile]

Xenou,

Thank you for the response. I tried the code you suggested and, like you suspected, the left join must have prevented it from working.

The reason I am using one table to determine what should be deleted from another is this query is to compare work orders in a holding table for backlog clean up against the old orders with the new list. I want to delete any work orders that are not in the new list as they are longer in the active system. That way, we can focus on what was not closed out. The new list is evergreen and gets updated several times a week.

I really appreciate the help. I learn something new every time I come to this forum. FYI, stumac's advice did work when I put the Where clause in the criteria for the Order field.

 

[Woodpile]

Stumac,

Thank you for your response. I put your code "Not In (Select [Order] from [01- Current Backlog Table])" in the criteria for the Order field and it worked just fine. Access created a new field for me but it does work. Now I can use this as a template to build the rest of the queries as there are ten different subsets for this process. Here's the SQL Access created:

DELETE [80- Section Backlog Holding Table].Order, [80- Section Backlog Holding Table].[Order]
FROM [80- Section Backlog Holding Table]
WHERE ((([80- Section Backlog Holding Table].[Order]) Not In (Select [Order] from [01- Current Backlog Table])));

Thank you also for the advice on filed names. When I started building this database, I didn't realize that "Order" is a reserved word. I do not want top change it now as there are way too many relationships and other queries and reports that would need to be edited. I will remember this in the future.

Thanks again

 

[stumac]

Glad you got it working. Both queries are essentially the same, just access adds in a bunch of things when you use the query builder.

When you say there are about 10 subsets, is that 10 queries deleting data from the same table, depending if it exists in another table? If so you could probably get round running 10 queries by changing the subquery to be a union query.


Also a couple of tips that most people on here would recommend - normally you wouldn't delete data once it has been closed, you would flag it as closed and then filter it out of queries, forms, reports. By deleting it you eliminate the possibility of using it to report on in the future.
Your data structure seems to be complicated. having tables hold the same data is unusual and breaks basic normalization rules. I understand the idea of trying to change things can seem daunting, however, you should have a look at how you could make it more efficient by normalizing it and consider making the changes... You may find it will help in the long run!

Cheers