By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,825 Members | 2,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,825 IT Pros & Developers. It's quick & easy.

Error 3086

P: 2
I'm an IT student currently working on a database and I am required to perform a delete query. The database I am working on seems simple enough but the query simly wont run.

This is my SQL:

DELETE Payments.*, Sections.*, [Personal Information].*, [Personal Information].[ID Number]
FROM [Personal Information] INNER JOIN (Sections INNER JOIN Payments ON Sections.[ID Number] = Payments.[ID Number]) ON (Sections.[ID Number] = [Personal Information].[ID Number]) AND ([Personal Information].[ID Number] = Payments.[ID Number])
WHERE ((([Personal Information].[ID Number])=1001 Or ([Personal Information].[ID Number])=1002));

Can anyone help me with this?
~Kris
Feb 6 '10 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

Unfortunately, Access is somewhat limited when it comes to DELETE queries. You cannot use a join in an Access delete query to delete rows across multiple tables the way you might expect.

As Access will only delete rows from a single table at a time, you will have to perform several delete queries one after the other to accomplish what you want to do - one each for tables Payments, Section, and Personal Information. If the tables are related 1-many you need to delete the many-side entries first before you try to delete the one-side entries.

There is an alternative, which is to have the cascade delete option set when defining the one-to-many relations for the tables involved. I would not recommend this approach, however; it is rather dangerous, because if you accidentally delete a one-side row by mistake all many-side rows for that entry will be deleted at the same time as a result of the cascaded delete.

-Stewart
Feb 6 '10 #2

P: 2
Hmm, I actually do have the cascade delete option set, however, my relationships are a one to one type. Although, I took your advice and it seemed to have worked out.

Thank you!
~Kris
Feb 6 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.