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.

john madiganAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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):

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.
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?
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john madiganAuthor Commented:
thanks for your help
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.