Delete all related records in multiple tables from a single query?

[scotteth]

Hello. I am a fan and user of MS Access but not a programmer. Please excuse my limitations. Can you please help me with a goal from the following information?

Background - I have created a database that I use as a health assessment for individuals. Demographic data is entered in advance of the assessment. Then, participant data is entered into these 5 tables with a relationship established by their personal id: Personal, heart health, diabetes, weight, stress self-care. After the assessment day is complete, I securely store the records created and then delete records where no data was collected, identified by null values in heart health table. Due to my limited knowledge, I do this by deleting data based on personal id in all tables. I cannot simply delete the personal table with cascading delete across all tables due to the relationship established (referential integrity?) along with default table value that are entered when a record is created.

Goal - Delete all records by personal id in a single query or other method. I am tinkering with my first delete query with no success. My current version includes all related tables by heart health fields (to establish null) and the * for the other tables. I have previously tried including all fields from all tables without success. I suspect my problem may be an easier fix for the folks like you in this forum that are much smarter than me.



You can find a screenshot of the query by following this link, https://i.imgur.com/CPc81NF.png

Please let me know if you can help. I would really appreciate it. Thank you.

Best Regards,
Scott

[Bob Fitz]

Replace "isnull" in the criteria row with Null (no quotes) and see if that works. Back up you data first !!
BTW it looks as though only one table has a PK ???

[scotteth]

Thanks for your reply. Using Is null without quotes is accepted but not null. If I understand your PK comment, each table includes personal id, which is used as the pk. I am trying to determine the minimum number of tables and fields that I need to include in the query to delete all of the personal records. Here is the current working query that displays the correct number of records in datasheet view but returns an error when running the delete query. Please see this screenshot and let me know if you have additional insight, https://i.imgur.com/RDqWDIo.png
Thank you.

[Bob Fitz]

If I understand your PK comment, each table includes personal id, which is used as the pk.

That is NOT correct. Only the Personal table has personalid as a Primary Key. In the other tables it is being used as a Foreign Key and shown as such with relationships shown as one to many. Can you post a copy of your db.

[Micron]

The long defunct ISO 14001 compliance auditor in me says "how do you know if missing (deleted?) data was ever collected or entered? Perhaps it was but database entry was forgotten or it was deleted, which is possible since you have a delete process". Better to enter N/A (or something) rather than delete or stick Nulls in records. N/A means it was considered at least. Perhaps none of that is of any value in this situation but I'm just throwing it out for consideration.

[ArviLaanemets]

... You can find a screenshot of the query by following this link,....

Most people here never follow outside links posted here! When you want to show anything (a picture, an example database, etc.), load it up here instead!

... Demographic data is entered in advance of the assessment. Then, participant data is entered into these 5 tables with a relationship established by their personal id: Personal, heart health, diabetes, weight, stress self-care....

...If I understand your PK comment, each table includes personal id, which is used as the pk...

I'm confused about those two quotes!

Do you have in all those tables a single row per participant (otherwise having personal id as primary key in all those different tables is not possible). When this is the case, why not to have all this in single table, to avoid all this headache?

Or you have a table, where you register people you are assessing, and then other table(s), where you register various data (hearth health, weight, stress, etc.) for every assessment session. When this is the case, then, additionally to persons table, you either:
a) Have a single table, with a separate primary key field (e.g. autonumeric field like ParcitipantSessionID), where assessment information (like participant id, session date) and all registered data (heart health, weight, stress, etc.) at this session are registered. This design is usable, when the whole set of measurements is always (or almost always) registered.
b) Have a table where all participant's session's general data is registered (like tblParcitipantSessions: ParcitipantSessionID, PersonID, SessionDate, ...), and a separate tables for every different measurement - like tblParcitipantSessionHeart: tblParcitipantSessionHeartID, ParcitipantSessionID, HeartRate, ..., or tblParcitipantSessionWeight: tblParcitipantSessionWeightID, ParcitipantSessionID, Weight, etc. This design is preferable, when the set of measurements varies through different sessions greatly, as there never will be any empty measurement fields. All measurement tables are linked to sessions table instead of persons table.

About deleting all info about certain person:
Unless you have all this person info in single table, there is no way to do this with single query (a query can display data from several tables, but can change data in single table only). As recommended way to handle all data by database user is through user interface (i.e. forms) only (tables are hidden from users, and running any queries designed by user is not allowed), I'd write an event procedure for form where persons are registered, which will create variables containing querystrings to delete all rows to linked to this person's id directly or through other tables (like to delete from measurement tables all rows linked to sessions with this person), and runs those queries, defined by querystring variables, when this person is deleted from person's form,

[scotteth]

Ok. Unfortunately, I cannot post a copy of the db due to this consistent upload error when attempting a file upload in accessforums, The following errors occurred:
assessment-working.mdb: Upload of file failed. Thanks for trying to help.

[Welshgasman]

I get that problem with chrome. Try firefox or another browser if you have one.

[scotteth]

ArviLaanemets - Thank you for your detailed and insightful reply. Regarding the primary key & table confusion, I use your scenario b (separate tables). As a result, I now understand that my record deletion cannot be performed in a single query. I will investigate event procedures. Thank you again.

[Micron]

You followed the procedure (How to Attach Files at the top of the page) for uploading files here?

[scotteth]

I did not zip it and suspect the file size may be too large. Regardless, my query issue question has been determined to be the incorrect solution through the good folks in this thread, especially ArviLaanemets. Thank you.

[Bulzie]

Could you use relationships on the tables and use referential integrity with Cascade Delete to do it?

Or set it off in code once you have the PersonalID to delete, then make a specific delete query for each table that has that PK, then you can just do like:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTable1_Delete"
DoCmd.OpenQuery "qryTable2_Delete"
DoCmd.OpenQuery "qryTable3_Delete"
DoCmd.SetWarnings True

Or maybe use the SQL code instead of a query name like "Delete * from Table2 Where PersonalID = vPersonalID"

[scotteth]

Thank you for your reply. This is an interesting solution that I will try. Thank you for being so helpful.