Delete Query with multiple tables

[MrM121]

Hi, I have a delete query that I want to use on another query. The other query has five tables in it, one as the main table, and the other 4 as subordinate tables, so a 1 to 1 relationship exits, as the data contained in the 4 subordinate tables makes up one complete record. When I try and run the delete query, it says that it cannot delete from specified tables. Here is the code:

DELETE *
FROM [qryDataEntry(Comment)]
WHERE ((([qryDataEntry(Comment)].strOrgCode)=[forms]![frmselect]![combo5]) AND (([qryDataEntry(Comment)].strMonth)=[forms]![frmselect]![combo11]) AND (([qryDataEntry(Comment)].strYear)=[forms]![frmselect]![combo13]) AND (([qryDataEntry(Comment)].SpecialtyCode)=[forms]![frmselect]![combo9]) AND (([qryDataEntry(Comment)].strRCodes)=[forms]![frmselect]![combo7]));

There is only data entered into the main data table (using the qryDataEntry(Comment) query), so the other tables do not contain data, not even an ID (which is created at the top of each table in order to link all the tables to the main data.

If anyone has any suggestions about how to get around this problem, they are greatly appreciated.

Thanks

 

[LogicalmanUS]

MrM121,

I am a little confused on a single point.

You say that the data from the 4 tables make up one record, but then say that sata is only entered into the main data table.

Do you mean that the four other tables are simply lookup tables for the main table (e.g. the main table contains an ID that refers to a record in one of the other tables)?

If this is the case then I would write the delete query to point solely to the main table.

If the other four tables each contain data pertinent to the main table, then they need to be constrained with cascade update/delete.

Please confirm the schema,

Thanks,

Logicalman

 

[MrM121]

Sorry about, that, I meant to say that a query brings them altogether, and then data is entered that way, with only the MainData table's ID. I managed to get it working last night by deleting JUST the Main Data table record, as it has some required fields, which as you said, has a cascasding property so it deleted the records in the other tables as well.

Thanks