Questionnaire Access Database

Question

Questionnaire Access Database

Anonymous

I saw Ken Sheridan's posting of the Questionnaire db file posted on August 14, 2014 (Ken Sheridan - Questionnaire Access Database) and have a few questions. The file seems to fit my needs but I need additional info"

Can we understand the process in creating this db? What is the order of object creation? Have you outlined the steps to re-create it?
Can you explain how do the fields, 'QuestionIndex' in the 'QuestionnaireQuestions' table and 'AnswerIndex' in the 'QuestionnaireAnswers' table work?
My questions text are long. I wanted to change the 'Questions.Question field to a memo, but I get an index error: "Changing to this data type requires removal of one or more indexes...". What will this change impact?
When using the db as is, I found that there is not a button on the main form (frmOpen) to edit the Questionnaire. If you want to edit an existing questionnaire, for example, add additional questions, I found that I had to do this via the 'Questions' table then add them to the 'QuestionnaireQuestions' table. Is there a better way to do this?
I want to add a relationship to a 'Systems' table where I have a field named 'SystemID'. I was considering adding this field to the 'Questionnaire' table, but wondering if it's the right table or maybe adding it to other tables as well. Ultimately, here's what I want to do:

A. I'm creating a Systems database. where I track various elements of multiple computer systems throughout their life cycle. There are a couple of assessment activities that need to be completed for each system. I figure, that the Questionnaire file will fit that purpose with some modifications. In the end, I want to perform various assessments (Questionnaires) for each system. Each assessment has its own set of questions along with an outcome depending on the answers and will be able to be re-used as new systems are created.

B. When starting an assessment, I'd like the db to provide the ability to the user to first select the system name that the assessment will be performed for, then select the type of an assessment ('AssessmentType') that will be performed (Regulatory, Risk, etc.) and finally present the list of questions per the assessment type.

C. At any time, I'd like to ability to recall, edit and print a report of an existing assessment based on the system.

Any help is appriciated

Charlie Tirado

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Answer 1

Hi Ken

Thanks for your quick reply.

If you look at this snapshot of my Responses table, you'll see that QuestionID 34 had 2 answers and 35 had 4. Your query counts these and therefore not aligning with the count of total questions.

QuestionnaireID	ResponseSessionID	Answer	QuestionID
7	14	Yes	31
7	14	Yes	32
7	14	Annex 11 Applicable	34
7	14	21 CFR Part 11 Applicable	34
7	14	21 CFR Part 11 Applicable	35
7	14	Annex 11 Applicable	35
7	14	GAMP Category 1	35
7	14	GxP Applicable	35

As I stated before, I don't want to count the QuestionID per Questionnaire, per Response Session more than once. All I need to know is if the question was answered or not. In the table above, the desired total count of Questions answered should be 4.

I'm close...I slightly modified your sql as follows:

SELECT DISTINCT Questionnaires.QuestionnaireID, QuestionnaireName,

(SELECT COUNT(*)

FROM QuestionnaireQuestions

WHERE QuestionnaireQuestions.QuestionnaireID = Questionnaires.QuestionnaireID)

AS CountOfQuestions,

COUNT(*) AS CountOfResponses,

CountOfResponses/CountOfQuestions*100 AS PercentageCompleted

FROM Questionnaires INNER JOIN

(SELECT DISTINCT QuestionnaireID, QuestionID, ResponseSessionID

FROM RESPONSES) AS DistinctResponses

ON Questionnaires.QuestionnaireID = DistinctResponses.QuestionnaireID

GROUP BY Questionnaires.QuestionnaireID, QuestionnaireName;

I removed the portion that would calculate the total number of questions asked over one or more sessions per questionnaire (this is the number of questions per questionnaire multiplied by the number of sessions per questionnaire).

The only thing left is to have the query only count the QuestionID once per Response Session.

Charlie

Answer 2

The following query should return what you are aiming for; i.e. the total number of questions asked over one or more sessions per questionnaire (this is the number of questions per questionnaire multiplied by the number of sessions per questionnaire); the total number of answers per distinct question; and the latter as a percentage of the former.

SELECT Questionnaires.QuestionnaireID, QuestionnaireName,

(SELECT COUNT(*)

FROM QuestionnaireQuestions

WHERE QuestionnaireQuestions.QuestionnaireID = Questionnaires.QuestionnaireID) *

(SELECT COUNT(*)

FROM ResponseSessions

WHERE ResponseSessions.QuestionnaireID = Questionnaires.QuestionnaireID)

AS CountOfQuestions,

COUNT(*) AS CountOfResponses,

CountOfResponses/CountOfQuestions*100 AS PercentageCompleted

FROM Questionnaires INNER JOIN

(SELECT DISTINCT QuestionnaireID, QuestionID, ResponseSessionID

FROM RESPONSES) AS DistinctResponses

ON Questionnaires.QuestionnaireID = DistinctResponses.QuestionnaireID

GROUP BY Questionnaires.QuestionnaireID, QuestionnaireName;

Answer 3

Hi Ken. I've been actively using the Questionnaire database and found it very useful...Thank you!!! One thing that I need to do is figure out a way to obtain questionnaire completion statuses. Your qryResponsesSummary query is a good start but I need to calculate a bit different:

I need the total of questions per questionnaire
I need the total of responded questions per questionnaire. In the event of multi-select answers, I only need to understand if the question was answered (1) or not (0). So in this case, if the question was answered, I only want a count of "1".
Lastly, I'd like a percentage of completion per questionnaire. If all questions were answered in the questionnaire, then 100%.

So in the end, I'd like the results to look like:

Questionnaire Name	Count of Questions	Count of Responses	Percentage Completed

I've come close in creating this query but can't make it work...

First I bring in the AssessmentID (QuestionnaireID), QuestionID and ResponseSessionID but I realize that with the multi-select answers, the query returns multiple rows of the QuestionID, depending on the answers that were selected:

Query 1:

SELECT Responses.AssessmentID, Count(Responses.QuestionID) AS CountOfQuestionID, ResponseSessions.ResponseSessionID

FROM ResponseSessions INNER JOIN Responses ON ResponseSessions.ResponseSessionID = Responses.ResponseSessionID

GROUP BY Responses.AssessmentID, ResponseSessions.ResponseSessionID;

So in this case, I need to somehow for the query to only return a unique QuestionID per Assessment (Questionnaire).

I've read that in order to accomplish this, I may need to create 2 or more queries and then combine them. I started with creating one that returns the total count of questions per assessment:

Query 2:

SELECT Assessments.AssessmentName, Count(AssessmentQuestions.QuestionID) AS CountOfQuestionID

FROM Assessments INNER JOIN AssessmentQuestions ON Assessments.AssessmentID = AssessmentQuestions.AssessmentID

GROUP BY Assessments.AssessmentName, AssessmentQuestions.AssessmentID;

With this query, I obtain the correct result, so that was easy. As far as the total of questions answered...well, that's a bit harder. I think I need a DISTINCT clause in query 1, but everything I try ends up in an error. I'm not sure if I can obtain all of the information in one query, but that would be nice.

Can you assist?

Thank you,

Charlie Tirado

Answer 4

The order of object creation for ANY database is first get your table structure correctly set up. From there you generally create forms so you can enter data. After that it depends on your needs.

While I think a questionnaire structure is appropriate for you, I think a simpler structure might be better. A questionnaire db has a specific base structure as follows:

tblQuestion: QuestionID (PK Autonumber), Question

tblRespondent: RespondentID (PK Autonumber), respondent info

tblResponse: ResponseID (PK Autonumber), ResponseDate, RespondentID (FK)

tblAnswer: AnswerID (PK Autonumber), ResponseID (FK), QuestionID (FK), Answer

In your case, I would add a table for question type that would indicate which assessment a question is for. If a question might be for multiple assessments you may need a junction table to manage that.

The Response table would have a FK to indicate the assessment type used. It would also have an FK for the system being assessed.

Answer 5

Like all of my demos this file is merely an illustration of methodologies as a starting point for others looking to build their own applications. It is not a template, nor a tutorial. I assume in all my demos that anyone using them will have sufficient technical knowledge to understand the methodologies employed and be able to adapt them to their own requirements.

While I am happy to clarify any specific points of detail in the files, I regret that available time does not permit my addressing the structural issues raised in your post.

From the information you have provided, I am far from convinced that my demo would provide a good model for what you have in mind. My impression is that your model would be much more task specific than my demo, which is generic in nature. I think you really should be looking to analyse the entity types, attributes and relationship types by which your assessment regime would be modelled, with a view to creating the necessary tables and relationships between them which would constitute a valid model of the specific reality, rather than trying to to shoe-horn them into my generic model.

Share via

Questionnaire Access Database

6 answers