Problem with executing Delete Query-Multiple tables

[BLUDEV]

Hello, I have two tables. One is a main table with a Primary key (001_SMCO...) other is a table container records I want to delete (P04...). In order to better see my issue, I'm posting the SQL view:

DELETE DISTINCTROW [P04_MasterRecord_EOL ALERTS_comp info].*
FROM [P04_MasterRecord_EOL ALERTS_comp info] INNER JOIN [001 SMCO_INITIATION] ON ([P04_MasterRecord_EOL ALERTS_comp info].MPN = [001 SMCO_INITIATION].MPN) AND ([P04_MasterRecord_EOL ALERTS_comp info].[ROK PN] = [001 SMCO_INITIATION].[RA PN]) AND ([P04_MasterRecord_EOL ALERTS_comp info].[Supplier PN] = [001 SMCO_INITIATION].[SUPPLIER PN]) AND ([P04_MasterRecord_EOL ALERTS_comp info].Supplier = [001 SMCO_INITIATION].SUPPLIER)
WHERE ((([P04_MasterRecord_EOL ALERTS_comp info].[RANDOM ID]) Is Not Null));

Basically, I'm trying to delete records in P04 table that matches specific records in 001_SMCO table. Can anyone tell me what I'm doing wrong?

 

[Insane_ai]

Define the relationship between the master and child tables using the cascade delete option. Then all you have to do is delete the master record, the database engine will handle the child records for you.

Read this first: http://office.microsoft.com/en-us/access-help/create-edit-or-delete-a-relationship-HA010072597.aspx

Then read this: http://www.granite.ab.ca/access/cascadeupdatedelete.htm

The second is someone's opionion on the dangers of these relationships mixed with some ranting but it still has some useful considerations.


The other option is to delete the child records with one query then delete the master records with another.

 

[Pat Hartman]

In a query that joins the 1-side table to a many-side table, you cannot delete the 1-side record. You can only delete records from the lowest level table in the join.

To solve this problem, there are at least two solutions.
1. Change the query so that it uses a subquery to find the "match" that identifies a record you want to delete.
2.Create a temp table with the Id of the record you want to delete by using the query with the join to find them and then have the delete query join to the temp table.

 

[BLUDEV]

Thanks "Insane..., It is still weird that I couldn't delete as my relationships were set up when I started creating the tables. It will let me delete them manually by pulling up the query which is why it is strange that I can't run the delete query as it keeps saying, "Specify the table containing the records you want to delete". The only thing I can figure is my "key" fields in both tables are not related and even though I have relationships set and indicated from what table to delete from, it is still not allowing me to do it. So, I had to create a step that took the matching fields in the "MasterRecord" table to a new table and then connect those relationships to delete the records from the table. I even added "distinctrow" (see below) but it still wouldn't delete.

DELETE DISTINCTROW [P04_MasterRecord].*
FROM [P04_MasterRecord] LEFT JOIN [001 SMCO_INITIATION] ON ([P04_MasterRecord].MPN=[001 SMCO_INITIATION].MPN) AND ([P04_MasterRecord].[ROK PN]=[001 SMCO_INITIATION].[RA PN]) AND ([P04_MasterRecord].[Supplier PN]=[001 SMCO_INITIATION].[SUPPLIER PN]) AND ([P04_MasterRecord].Supplier=[001 SMCO_INITIATION].SUPPLIER)
WHERE ((([P04_MasterRecord.[RANDOM ID]) IS NOT NULL));

If there is another way that you and Pat are aware of or if there's something I'm missing, please let me know.

BLUDEV

 

[Pat Hartman]

Personally, I would have used option 1 since I never make temp tables if there is another alternative but I'm glad it worked for you

 

[BLUDEV]

I see what I did, never mind, I have it set up correctly now; guess my brain was on freeze mode.