Delete Query Issue (1 Viewer)

[Tskutnik]

All, Someone that knows what they are doing can probably help in about 15 seconds. Attached is my DB with a delete query. Why is it not running?
You will see the table relationships. I want 2 fields in the table [..._Tab] used to select the records in table [...Eeco] to delete.
Any help is appreciated.

There is no primary key in the table being deleted. Is this the issue? If so, I would just add an auto number field, but the 2 criteria would still come from the same place.
If I need to only reference the primary key between joined tables when deleting records that is my problem, but I hope that is not the case.

 

[Gasman]

Join Acct_ref and ActKey. That will then delete 1595 rows which is what you get is you change to a Select query?
I would also specify the tablenames, just in case.?

DELETE DISTINCTROW HoldActiv_TaxyGroupEeco.*, HoldActiv_TaxyGroupEeco.Acct_Ref, HoldActiv_TaxyGroupEeco.Hold_Date
FROM HoldActiv_TaxyGroupEeco INNER JOIN HoldActiv_LatUpdate_ToRun_Tab ON HoldActiv_TaxyGroupEeco.Acct_Ref = HoldActiv_LatUpdate_ToRun_Tab.Acct_Key
WHERE (((HoldActiv_TaxyGroupEeco.Acct_Ref)=[HoldActiv_LatUpdate_ToRun_Tab].[Acct_Key]) AND ((HoldActiv_TaxyGroupEeco.Hold_Date)>[HoldActiv_LatUpdate_ToRun_Tab].[Acct_DateRunFrom]));

HTH

 

[Tskutnik]

Gasman - thanks, that worked.
Unfortunately there is a second problem... The first DB I sent was after conversion of all underlying queries to tables, to make the diagnosis easier.

When I plug your solution into my actual DB it does not, and I have isolated the problem to [HoldActiv_LastAcctUpdates] union query that preps the data.

The whole DB query flow is below. See the [HoldActiv_LastAcctUpdates] outlined in red in the center. When I convert that from a query to a table (eliminating the 4 sources) it works. When I run it as a query (as is in the new attached DB) it does not.

I'm totally stuck as to why.

Any help is appreciated.

 

[Gasman]

I am NOT a query expert. I run and modify to suit.
However I would have thought that whether table or query the result should be the same?
DB1

SELECT DISTINCTROW HoldActiv_TaxyGroupEeco.*, HoldActiv_TaxyGroupEeco.Acct_Ref, HoldActiv_TaxyGroupEeco.Hold_Date
FROM HoldActiv_TaxyGroupEeco INNER JOIN HoldActiv_LatUpdate_ToRun_Tab ON HoldActiv_TaxyGroupEeco.Acct_Ref = HoldActiv_LatUpdate_ToRun_Tab.Acct_Key
WHERE (((HoldActiv_TaxyGroupEeco.Acct_Ref)=[HoldActiv_LatUpdate_ToRun_Tab].[Acct_Key]) AND ((HoldActiv_TaxyGroupEeco.Hold_Date)>[HoldActiv_LatUpdate_ToRun_Tab].[Acct_DateRunFrom]));

DB2

DELETE DISTINCTROW HoldActiv_TaxyGroupEeco.*, HoldActiv_TaxyGroupEeco.Hold_Date
FROM HoldActiv_TaxyGroupEeco INNER JOIN HoldActiv_LastUpdate_ToRun ON HoldActiv_TaxyGroupEeco.Acct_Ref = HoldActiv_LastUpdate_ToRun.Acct_Key
WHERE (((HoldActiv_TaxyGroupEeco.Hold_Date)>[Acct_DateRunFrom]));

You have to compare like with like? and I do not understand your table structure/business etc remember?

 

[isladogs]

Union queries are read only.
Any query that includes a union query will also be non-editable which means the delete query won't work.

I haven't looked at your database.
However, you will need to construct your delete query in such a way as to circumvent this issue.
So if your union query is based on say 3 tables, you should do 3 delete queries in turn, each with one of those tables used in place of the union.

Hope that makes sense

 

[The_Doc_Man]

Perhaps you need to do this in a different way involving a "divide and conquer" approach.

In order for Access to modify any table, it must be able to clearly identify the location of any records to be deleted. As noted by Colin, you cannot delete through a UNION query. Even deleting records from JOINed tables can be very difficult sometimes, particularly if there is any ambiguity in what records are to be deleted.

However, you COULD use a UNION query to supply information that indicates which records are to be deleted.

Consider, for example, a sub-query using a SELECT query that is your UNION query. Something of this form, in which UNION_QUERY_Y is your UNION query and TABLE_X is the table from which the deletions are to occur.

DELETE * FROM TABLE_X 
    WHERE TABLE_X.IDX IN 
        (SELECT IDY FROM UNION_QUERY_Y UQY 
            WHERE UQY.Z = TRUE AND UQY.W = FALSE AND etc. etc. etc.)  ;

This unambiguously defines the table from which deletions should occur and yet can use the UNION query as input to the record selection process. You can use criteria in the sub-query to depend on the UNION query values to make your selection, OR you could have the selection criteria built-in to the UNION query's parts.

The trick is to just keep the UNION query separate from the data set being targeted for deletion. The sub-query syntax does that for you. If you are not sure because you've never used an IN clause before,

SQL: IN Condition

This SQL tutorial explains how to use the SQL IN condition with syntax and examples. The SQL IN condition (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values.

www.techonthenet.com

SQL IN Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

IN clause (Microsoft Access SQL)

Office VBA reference topic

docs.microsoft.com

That last reference is a bit complex because the IN clause even allows you to reach outside of the current DB to retrieve something.

 

[Tskutnik]

All - thanks very much for your input. I read all your notes and unless I misunderstood I'm still a bit confused..
The Union query in my DB identifies the records to be deleted FROM the other table. I'm not trying to delete form the union query itself or from a table that underlies the union query. That's why this makes no sense to me.
I thought the issue may be ambiguity of records, but when I 1) Run the select query instead of delete the results are correct or 2) When I convert the union query results to a Table and run the delete works correctly. There is something with using the Union query itself (not the records within) that is giving me the problem.
Guess I was hoping that there was something small I missed in the query or the DB and I could be an easy to fix as it is written
I can code around the query if needed.

 

[isladogs]

Yes - it seems you have misunderstood yet your own tests 1 & 2 confirm the point I was making
Its the structure of the query that matters

This link explains all the reasons why a query is read only: http://allenbrowne.com/ser-61.html
An action query (append/update/delete) will fail if it is based on any of those factors - in your case the union query

So you need to run this in several parts as already suggested.

 

[Tskutnik]

isladogs - thanks, but again, I'm not deleting FROM the union query. I'm deleting from an unrelated table, with the Union query used to ID the records to delete from the table.
The union query is definitely read only

 

[theDBguy]

isladogs - thanks, but again, I'm not deleting FROM the union query. I'm deleting from an unrelated table, with the Union query used to ID the records to delete from the table.
The union query is definitely read only

Hi. I think what @isladogs was saying is by using/adding/including the read only union query into your delete query, then the delete query becomes read only as well.

As another experiment, take you converted select query from your union query and see if you can modify/change any of the data in it. If not, then it's read only because of the union query. If you can't change the data, it means you can't delete any records either.

Hope that makes sense...

 

[Tskutnik]

OK so if by extension the read only Union query makes the delete query read only I kinda get it, although again, I'm deleting FROM a totally separate table.
I can work around this either way. Thanks for all the help

 

[theDBguy]

OK so if by extension the read only Union query makes the delete query read only I kinda get it, although again, I'm deleting FROM a totally separate table.
I can work around this either way. Thanks for all the help

Yes. Unfortunately, it doesn't matter which table you're trying to delete from. Since you included a read-only source to your query situation, Access either gets confused or wants to play it safe and just treats the entire thing as read only as well.

 

[isladogs]

That is exactly the point I've been making throughout!

Scrap the union query.
Delete records from the first table...then delete from the second table...then the third...etc ... until done.

Having said all of that, most developers recommend that records are never deleted.
Instead such records should be 'flagged' as inactive by setting a boolean field called Active to false