- Count how many interviewees' have been asked this question regardless of answer given.
- Count how many interviewees' answered YES to this question.
- Calculate the percentage of Yes answers on the basis of information obtained in stages 1 and 2 above.
|Figure 1: The sample data we shall be working with is|
stored in a table called tblQuestionnaire. Each record represents a separate
interviewee's set of answers. In this exercise we are just interested in their answers' to Question1.
The first stage involves creating a query to count the number of interviewee records - that is to say, the count of how many times Question1 was asked. This just requires a single query column based on the count of the ID field in tblQuestionnaire. We shall save this query as qryQuestion1Count:
|Figure 2: The stage one query saved as qryQuestion1Count.|
The second stage involves creating a query to count the number of YES answers to Question1. This is achieved by counting the ID field of tblQuestionnaire as we did in the first query, but this time we shall also add a second query column based on the Question1 field. This column needs to contain a criteria to filter out the YES answers (since -1 represents YES when dealing with the Yes/No Data Type, the criteria will be = -1). As such we are creating a query to count the ID fields of all records WHERE Question1 = -1. We shall call this query qryQuestion1Yes:
|Figure 3: The stage two query saved as qryQuestion1Yes. This counts the number of|
ID fields (and hence records) in tblQuestionnaire WHERE Question1 = -1.
|Figure 4: The Third Stage Query - qryQuestion1Percent.|
|Figure 5: Query result returned by qryQuestion1Percent on sample data.|