Error 3086

Anon93

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

Stewart Ross

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

Anon93

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