Results 1 to 13 of 13
  1. #1
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6

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

    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

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2019
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,514
    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 ???
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6
    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.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2019
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,514
    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.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,645
    Quote Originally Posted by scotteth View Post
    ... 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!
    Quote Originally Posted by scotteth View Post
    ... 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,

  7. #7
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6
    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.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,773
    I get that problem with chrome. Try firefox or another browser if you have one.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6
    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.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,651
    You followed the procedure (How to Attach Files at the top of the page) for uploading files here?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6
    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.

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,449
    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.Open
    Query "qryTable3_Delete"
    DoCmd.SetWarnings True

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



  13. #13
    scotteth is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2023
    Posts
    6
    Thank you for your reply. This is an interesting solution that I will try. Thank you for being so helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2021, 11:35 AM
  2. Replies: 3
    Last Post: 10-18-2017, 04:01 AM
  3. Replies: 2
    Last Post: 12-11-2014, 02:29 PM
  4. Replies: 6
    Last Post: 02-21-2014, 04:58 PM
  5. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums