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.

 

[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

 

[BLUDEV]

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