Results 1 to 8 of 8
  1. #1
    napierse is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4

    Checklist and action item form

    I am trying to develop a database that tracks policy updates:




    For every policy change, I would like to record a description and dates of the update. Each update requires a checklist with a standard set of questions: 1. Does this update impact safety policies? 2. Does this update impact environmental permits, etc.

    If they answer "Yes" to a checklist question, I would like them to write in an action item (memo style - IE - Modify safety policy X to include new equipment, and have them write a date in later when the item was actually completed. )

    I can set up the update form and checklist. I can also create an action item table, but I am not sure how to join the action item to the checklist questions with "yes" answers.

    I have only used access for simple queries and reports. I have some VB experience, but in Excel. (And I need to check VB forums a lot for help).

    I appreciate any advice for how to accomplish this.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you supply a sample database with some garbage data as an example, the answer is really going to depend on your data structure.

    for example I might have

    Code:
    tblPolicy
    P_ID P_Num  P_Description  ---> other policy related fields
    
    tblPolicyUpdate
    PU_ID  P_ID  PU_StartDate  PU_CompletedDate  PU_Description
    
    tblChecklist
    C_ID  C_Description
    1     Checklist Question 1
    2     Checklist Question 2
    
    tblPolicyUpdateChecklist
    PUC_ID  PU_ID  C_ID  PUC_Response  PUC_Memo
    Now when I added a new policy update record I would also add a series of records to tblPolicyUpdateChecklist to add a record for each checklist question with the policy update id (PU_ID) of the newly created record.

    Then in my data entry only make the memo field available if the response to any of the checklist items is 'yes'

  3. #3
    napierse is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    I attached a simple version of what I was thinking. So if my PU_ID is the main record of the policy change, each PU_ID has its own checklist. Each checklist has its own action item table... or does each checklist question have its own action item ID? I'm still a little confused here. And my database shows it...
    Attached Files Attached Files

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This database doesn't help much.

    I had assumed you would have something like

    Policy (table containing a list of all the policies)
    Each policy has a set of revisions
    Each revision has a series of questions that need to be answered
    (new as of last post) For each YES response there may be 1 or more action items

    Does that accurately sum up what you want to do with this database?

  5. #5
    napierse is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    I apologize for the bad database. I just started working on it, and I realize I had no idea how to deal with action items.


    Here is what I am looking for:
    Change (table with a description of what needs to be changed, who initiated the change, dates,)
    Checklist (table with the standard questions)
    For each Yes response om the checklist, there may be 1 or more action items.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    So I do have it right, you have just omitted the list of policies, because your changes have to be related to a specific policy do they not?

  7. #7
    napierse is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    You are correct, I have omitted the list of policies for now. I'm trying to make it a little more generic for now. But yes, you are right in what I am trying to do.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Enclosed is your database with a structure closer to what I'd use

    When you add a record to tblChange you'll want to run a query to add all 'active' checklist items to tblChangeChecklist to tblChange with the PC_ID of the newly added change record.

    This structure then allows you to attach an action item to a particular change checklist item

    DBPOLCHANGE2.zip

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

Similar Threads

  1. yes/no checklist query help
    By nightclock in forum Queries
    Replies: 6
    Last Post: 07-19-2016, 07:44 AM
  2. Replies: 4
    Last Post: 01-19-2016, 06:12 AM
  3. Replies: 7
    Last Post: 11-29-2015, 07:24 AM
  4. checklist
    By slimjen in forum Forms
    Replies: 23
    Last Post: 06-20-2013, 11:17 AM
  5. Parent Item / Child Item Not Saving Correctly Together
    By Evilferret in forum Programming
    Replies: 6
    Last Post: 08-24-2012, 02:30 PM

Tags for this Thread

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