General design for Check list database by customer

I have a checklist of Items that I use will all my clients each quarter.  I want to design an access database to keep track of the check lists after each visit.

Need some thoughts on how to set up the tables - here is what I have so far:

- my check list has SECTIONS on top - under sections are CATEGORIES - under categories are QUESTIONS (y/n or NA answers & note area)

An Example would be:  SECTION = Firewall    then CATEGORIES = Installation    then QUESTION = Firmware updated?

There are 2 parts I need help with:

1) Need tables that store the Template Check list.  Then I can have a form that I can assign a client to the template and then turn on or off parts of the template if it doesn't apply to them.  I would save the modified template so I have a modified Checklist per each client.

2) I bring up that modified check list by client and have a form to answer the questions + have a comment section - then save the checklist by date after each visit.

Not sure where to start here - any advice would be greatly appreciated.

Thanks,
john madiganAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
You need "definition" tables where you define the structure of a particular checklist.  Then for each user, when you want to add the checklist "answers", you would copy the checklist from the definition tables and append it to the User tables ready to receive the "answers".  As long as you define the schema correctly, your app can support multiple checklists so you can make different versions for different types of companies or different versions from year to year.  Plan for the future.  Take your time working out the organization of the "definition" tables.  Don't start developing forms until you think you have the organization clearly described.  I've done checklists where I defined the type of answers for each question such as Y/N, combobox, free text.

Here's a picture of one of the checklists so you can see how far you can take this.CheckList.JPG
0
 
Fabrice LambertFabrice LambertCommented:
Rule of thumb:
Divide to rule better

Always start with the data structures, as your form design will heavily depend on it.

Basically, each of your concepts will translate into tables:
USER ==> table
CHECKLIST ==> table
SECTION ==> table
CATEGORY ==> table
QUESTION ==> table

From your descriptions there are one to many relationships between (this will translate to primary and foreign keys):
USER and CHECKLIST
CHECKLIST and SECTION
SECTION and CATEGORY
CATEGORY and QUESTION

So you database will probably hold tables with:
Table USER, with an ID column as PK
Table CHECKLIST with an ID column as PK, and an ID_User column as FK pointing to USER table.
Table SECTION with an ID column as PK, and an ID_Section column as FK pointing to CHECKLIST table.
Table CATEGORY width an ID column as PK, and an ID_Section column as FK pointing to SECTION table.
Table QUESTION with an ID column as PK, and an ID_Category column as FK pointing to CATEGORY table.
0
 
john madiganAuthor Commented:
I have the tables set up.  I have no data in the CHECKLIST TABLE yet.  I have data in the other tables but no data in the foreign key columns.

I'm not sure what the next step should be?  I was going to make a form with a combo box to pick the Client - and I would have it generate a new record in the CHECKLIST table with this Client ID    --- so now I have a record in the CHECKLIST table - Client id tied to a Checklist ID.

The problem I have is that in the SECTIONS table I currently have 17 records with unique names for the sections.  There is another colunm for Checklist ID  - but a section name can be on multiple Checklists - how do I handle this?
0
 
john madiganAuthor Commented:
thanks for your help
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.