Query - Counting answers. (1 Viewer)

[kacey8]

Hi All,

Hoping you can help as I am sure this is straight forward,

I have a database and I am trying to extract answer questions, The premise is as follows.

tables are as follows


tblClients
- ClientID
- DateAdded
- ClientName
- ClientRef
- OtherComments
- AdviserContact

tblQuestions
QuestionID
ClientID
QuestionNum
QuestionAnswers


QuestionNum runs from 1-8 (there are 8 questions)
Question Answers are ranked 1-5 (good to bad)

I need to create a query which will count how many of each answers there are grouped by each AdviserContact ie

Adviser Contact
Quest1Answer1 = Count of Question1's answered with a 1
Quest1Answer2 = Count of Question1's answered with a 2
Quest1Answer3 = Count of Question1's answered with a 3
Quest1Answer4 = Count of Question1's answered with a 4
Quest1Answer5 = Count of Question1's answered with a 5
Quest2Answer1 = Count of Question2's answered with a 1
Quest2Answer2 = Count of Question2's answered with a 2
Quest2Answer3 = Count of Question2's answered with a 3
Quest2Answer4 = Count of Question2's answered with a 4
Quest2Answer5 = Count of Question2's answered with a 5

and so on until Question8.

any idea on the best way to structure this, or maybe a simpler/easier solution? The query needs the date field so it can be filtered by date when run.

 

[isladogs]

Use an aggregate query with both tables linked by ClientID
Add a parameter to enter the date as below


Group by clientId, question, answer and add answer again but this time use Count

So you can filter by date, add date using Where and in the criteria enter something like this in square brackets : [Please enter a date]
The user will then be propted with this message on running the query