Delete query is not working (1 Viewer)

Hi,

what is wrong with my query:


i am getting info "Coould not delete from specific table".

query designer:


This is happening when i am joining more than 11 fields.
If i am joining 10 fields - all is working fine.


why this is happening?

Please help,
Jacek

That's a lot of joins! Does it matter which join is added as number 11?

You can do up to 16 joins in a query so you haven't hit the limit - https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

But think about what this query would do
Do you want to remove records which exist in both tables where there are 11 identical fields? If so it should be inner joins.

Left joins would make sense if you wanted to delete records NOT in the temp table but then you would need to add Is Null clauses to each of those 11 fields

As it is the query doesn't make sense.
Suggest you first create a SELECT query that gets the records you want to delete then change to a DELETE query

Hi,

strange behaviour.
No matter which join was added, first or last. After 10 i have this errror...

Yes, this is deleting query based on your article.
But i think that maybe i will delete whole table instead of deleting only specific rows existing in one table and not existing in second. Maybe not very bad ass approach but this query is not very efficient...

Best,
Jacek

Yes I agree
For info I have just updated the website article following your earlier comments
Changes to item 5

Will also add a comment in your other thread

Just for information, I assisted another user on a very similar task today in this thread at UA https://www.utteraccess.com/forum/index.php?showtopic=2052117&hl=

They had an append query with 17 joins which I got to work for them (before fixing the design to create a better version).


So although Access specifications say 16 is the maximum number of joins in a query that limit seems to be rather fluid.
I don't see why it would be less than that for a delete query so something else was probably causing the limit - no idea what though

hi isladogs,

hmm this is not good because i want to use delete query from your website (delete and append, upend query).

How can i workaround this issue?

Best,
Jacek

Can you post a cut down copy of your database with the two tables and your queries

Hi,

in attachment,
strange because i can not even delete records when i have only one field joined...why?

Remember that when you are deleting and there is no PK field in source or destination table you must set Unique Records = Yes.

In this case, the following will delete both records:


However if you are not specifying which records to delete you only need this:


If you only want to delete unmatched records then it should be

Easy to get confused. ALWAYS test with a SELECT query first to check which records will be affected by your action query.

BTW - if your two tables match its very difficult to understand what you are actually trying to do. Please try to upload realistic data

Speaking of getting confused, I've just updated that web article and example database for a third time - corrected another error in method 5 and added extra comments about managing NULL values

hi Colin,

thank you.
I checked your website but i could find article aboout "UPEND" queries once again (it would be good to have search field there).

regarding this topic.
Data is fine.

I modified qryDeleteUNMATCHED to join more than 1 field and once again - problem appeared.

https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=73325&stc=1&d=1547193199

How can i check unmatched records using dfferent approach than joins?
Maybe exists function?

Please help,
Jacek

jaryszek said:

I checked your website but i could find article aboout "UPEND" queries once again (it would be good to have search field there).

Click to expand...

I agree it would be useful to have a search facility on my website
In the meantime, at the bottom of the Home page, click the Site Map link
You will find the UpEnd query article in the Code Samples section ...or you could click on the link in the Synchronise Data article

jaryszek said:

regarding this topic. Data is fine.

Click to expand...

I meant for others to understand what you want to do

I modified qryDeleteUNMATCHED to join more than 1 field and once again - problem appeared.

How can i check unmatched records using dfferent approach than joins?
Maybe exists function?

Click to expand...

I don't know why it occurs once you hit 11 joins. Can only assume it becomes too complex for Access to manage.

I don't know of a generic function to do this.
Much better to fix the data structure so its not necessary to go through this if possible.
However here is a work-round I use on the rare occasions I have to do something similar.
The approach should work in all situations

Create a select query to identify all the unmatched records - qrySelectUnmatched
Convert it to a make table query qryMakeTableUnmatched & run it.
This creates a 'temp' table with all the records to be deleted (including the PK field)

Now make a new query joining your data table with the temp table using the PK field and convert to a delete query qryDeleteUnmatchedFixed. Run it.

Now you have deleted all unwanted records you can clear the temp table created. If this is a one off, delete the temp table. If you'll need to do this again in the future, empty it instead.

If you are happy this does what you want create a procedure to do each step in turn - see a draft version DeleteUnmatchedRecords in Module1.

See attached for all the above

I'll add this approach to my website article later

thank you Colin,

i tested this , this is ok.
why we can not use select query for unmatched records and delete from this query?

i will open new topic, maybe there is an sql to write delete query in other way.

Best,
Jacek

jaryszek said:

why we can not use select query for unmatched records and delete from this query?

Click to expand...

You cannot run an action query where it uses another query which is a subset of the table you are trying to modify.

This is because performing such an action would alter the results of the other query. This would make the action unclear so Access prevents it being done.