Creating a Questionaire in Access 2003

I am trying to build a Fire Inspection Questionaire in Access 2003 that simulates a paper questionaire that contains questions with Yes, No, and N/A checkboxes.

The questionaire contains 10 chapters with about 5 sections each.

Each section has a mix of

1. Yes-No-N/A checkboxes
2. Yes-No checkboxes
3. Text boxes
4. one-two or three detail tables

eg.
1. Is the Sprinkler System Test ? Y__ N__ N/A __
2. Is the Fire Alarm Light ok? Y__ N__ N/A __
3. When was the date last checked: _______

Now the question is:

What is the best design for the tables

a. put all Yes-No-N/A questions in one detail table where each record would be one question.

col 1 - a chapter no
col 2 - a section no
col 3 - a question no
col 4 - a question text box for the question.
col 5 - the Yes checkbox
col 6 - the No checkbox
col 7 - the N/A checkbox

b. put all text box questions in another table
col 1 - a chapter no
col 2 - a section no
col 3 - a question no
col 4 - a question text box for the question.
col 5 - an answer text box for the answer

OR

c. put all questions for each chapter section on one record.
Each questionaire chapter section represents one
inspection and could have upto 30 questions.

Thanks Vic

There are a couple of things you need to consider in your design. The first one is maintenance and the second one is reporting.

How often do the chapters/sections/questions change? Or more importantly once you have this online how often will they change. There's something about seeing a form on a screen that makes people thing changes are easy, fast, and simple.

The next thing you need to consider is what reporting will you need to do. As soon as you put this into a database someone will want a report.<grin>

I guess my answer would be (d)

tblQuestions
col1 questionID
col2 chapterNum
col3 sectionNum
col4 questionNum
col5 questionText - the text of the question itself (i.e. "Is the Fire Alarm Light OK?")
col6 questionPurpose - this allows you delineate similar questions.

For example you may have multiple questions whose text is "What was the last date?" however one is for the Fire Alarm Light and another is for Smoke detectors. So the questionText field would have "What was the last Date?" and this field would have either "Fire Alarm Light Date" or "Smoke Detector Date". I know this seems a bit redundant and takes a bit of time, but trust me when you start running reports or doing maintenance you'll be very happy you did. <grin>

col7 questionType - checkbox, date, text

Now for the answer table you have a couple of options and it again, depends on your reports, and if the questionaire is filled out by one persion in one fell swoop or if the questionaire can be completed over time by multiple people.

If it can be completed over time or by a number of people then you'll want to add who and when to each answer, other wise you can reduce the data redundancy by having a table for who and when and where?

the two methods would look like this.
One person one point in time:

tblQuestionaire
col1 questionaireID
col2 questionaireDate
col3 questionaireAuthor
col4 questionaireLocation

tblAnswer
col1 answerID
col2 questionaireID
...

Multiple people, over time

tblAnswer
col1 answerID
col2 questionaireDate
col3 questionaireAuthor
col4 questionaireLocation
...

As for the answer table itself you can collapse all the actual answers into a text field which makes printed outputs easy to create or you can keep them seperate which makes queries and online viewing easier.

All in one

tblAnswer
col1 answerID
... (questionaire column(s)
col10 questionID
col11 answerText - this simply has the "Yes", "No", "N/A", "05/05/2008", "100 psi", etc

All seperate

tblAnswer
col1 answerID
... (questionaire column(s)
col10 questionID
col11 answerText - "100 psi", "Red", etc
col12 answerDate - "05/05/2008"
col13 answerCheckbox - "Yes", "No", "N/A" or 0,1,Null, or whatever

There are some variations of this. Personally I use an answerDate and answerText. AnswerText is always populated and if it is a date, then answerDate is populated as well. I know this is redundate data and not good design, but it makes reporting VERY easy and minimizes conversion errors.

Hope this helps!

Hi Dykebert

Thank-you very much for your input it is very much appreciated!!!

The questionaire is used by Fire Inspectors and there are 10 different types of Inspection Reports:

1. Annual Inspection Notes
2. Annual Sprinkler Inspection Report
3. Electromagnetic Locking Device Inspection Report
4. Emergency Lighting Inspection Report
5. Fire Alarm System Test Inpsection Report
6. Monthly Fire Alarm System Tests
7. Fire Alarm System Verification Report
8. Fire Extinguishers and Fire Hose Report
9. Monthly Sprinkler Tests
10. Standpipe Flow Test Report

The 10 Inspection Reports have a mix of information

1. Yes-No-N/A questions.
2. Yes-No questions.
3. Date questions.
When was the Fire Alarm last tested? Jan 2008
4. Textbox questions.
What is the PSI? 100 PSI
5. Inventory Tables with about ten or less fields
List of Fire Extinguishers
List of Fire Panels
List of Sprinklers
6. Comments Questions
The Inspector can input his comments.

The primary purpose of the Online Questionaire is to simulate the Paper Questionaire with the look and feel.

I need to be able to print the previous years 2007 Inspection Questionaire with all the answers and generate a new one for 2008 Inpsections with the 2007 answers.

The Inspector would then go out and perform the inspection and then mark up the questionaire with any changes.

The completed paper questionaire is then input by a person from the Fire Contracting Companies, so, I probally need Access Pages instead of Access Forms. A nice to have is to have this application available on the Internet.

The old system used some proprietory software that used templates where reporting was not available except for printing the questionaire itself.

My client wants to be able to produce various inventory reports from the various inventory tables and filtering on last updated dates in these tables etc ...

I need to be able to print the mix of questions and tables to simulate the Paper version of the Inspection Reports.

I also need to consolidate the seperate Contractor Access MDB files into a single database file for Corporate Reporting or build the MDB for the Web using Access Pages.

OK with the additional information.

1. You'll definately want to go with a Questionaire table
2. Since you will probably end up on the web at some point I would recommend either the 'All in one' version of the answer table or the hybrid which has a date field along with the text field.
3. It sounds like you are talking about multiple clients/locations so you will want tables for that data as well.
4. The inventory tables are bit more interesting. They are recorded on the questionaire, but in reality they are features of the location or site itself. Another reason for having a location table. The design is simple, but making sure it works with the questionaire could be tricky.
5. The design I suggested would be for a single db or the contractor db. If you want to have a main db that is a repository you'll need to include some fields about the source of the data so you can trace back to the original data if there is an issue.

Beyond that, I am between jobs at the moment... <grin>

Hey D...

1. The Fire Inspecition Questionaire is performed once for each building address and Apartment/Unit Number. The building/location table is the Primary table that drives the application.

For each unique building unit address their is an inspection questionaire filled out.

2. The Questions/Answers table

I am clear on the Questions/Answers tables, however, If the questions/Answers are inter-mixed on different records ...

How do we handle the programming and the printing of the different types of checkboxes, textboxes, dates, times, memos, and inventory tables?

I was thinking originally thinking that I would need a seperate query to print the checkbox questions, another query to print the date questions, another query for textbox questions, etc...

I know that if the table contains a checkbox field then printing is pretty simple, however, if the answerText is a text field sometimes and a checkbox at other times, I am not sure how I am going to program this????

Thanks Vic

The reason I would not create an answer table for each type of value is becuase it means you have to keep track of which questions are of which types and which tables you need to access. It's certainly doable and if you find it easier to work with go for it! <grin>

As to my suggestion for an All in one answer table where all answers are kept in a text field, my reasoning is twofold.

First if you move this to the web you'll get text values so you'd be set for that.

Second you'll have a single table with all the answers.

In part my design is based on the assumption of using option boxes to restrict the user to a single value. With option boxes you can use any integer value for the checkboxes so you have a single value for the question. (Hopefully this make sense). The conversion from text to integer is usually easily doable so it's the same difference to me if it is stored as text or integer.

Now if you go with an answer table that has a field for each datatype (i.e. answerText, answerInt, answerDate, etc) you'd have something like

SELECT IIF(IsNull(answerText), IIF(IsNull(answertDate), answerInt, answerDate), answerText) AS answerValue, ...
FROM answerTable
..

I don't believe there is a single right way. The bottom line is that any answer has to fulfill the business needs and be doable for you. In other words something that you're comfortable with, that makes sense to you.

If I get some time, I'll see if I can throw together a sample db and post it.

Since an example is worth a thousand words, I threw together a basic example.

FYI any form or report fields that are pink are interim fields and are not visible when the form/report is run.

I hope you find this example of use.