Microsoft Access Survey

Question

I'm trying to create a survey to replace a form we use.

The form has 60 questions which we use to audit patient notes. The responses are a simple yes/no with a checkbox.

The form needs to specify: Date, patient name, DOB, the name of the Auditer and name of the persons notes are being Audited

I'd like to be able to run reports to find the overall percentage of yes responses, for each individual who's been audited and also to see which questions are most commonly responded to as no.

I've started to create a table with each response listed as a field and have created a form based on these responses. However, I'm not certain if this will allow me to run reports for the data required.

Any help would be much appreciated

Answer 1

Thanks for that Ken.

It's probably a bit more complicated than I envisaged but achievable at least. I'll set some time aside today hopefully to look through your questionnaire.

Answer 2

It will not. That would be a HORRIBLE design violating several relational design principles. Ken has the right idea - don't ignore it. A survey database consists of SEVERAL tables and would be more than could be expected of a beginner.

Answer 3

>>I've started to create a table with each response listed as a field...........<<

Stop right there!   Responses are data, so to have a column per response is 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct model for a questionnaire database is, in broad outline, composed of a table of questions, a table answers and a further table which models the relationship type between questions and answers.  A table of respondents is then related to this table by means of another table which models the relationship type between them.  In reality the model is a little more complex.

You'll find an example, which enables multiple questionnaires to be built, in Questionnaire.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.