Counting Text Fields in a Query

[TimMoffy]

Hi,
I have created a tool for our audit team to use when reviewing the work of team members. In total there are 54 questions that they have to answer and each one has a possible outcomes of Yes, No, N/A. The field name is txtQ1Outcomes, txtQ2outcomes etc. and all data is stored in a table tblDataStore.

Once all of this data is collated I then want to run a report that shows the audit results by counting the number of times a particular handler for example, has had an outcome of Yes/No/NA for Q1, Q2, etc.

If I create a query and use Q1 as an example then I have two columns, one to group by and one to count and this works really well. However as soon as I add Q2 to the query I get a "summary grid" that shows how many times Q1 has been answered "Yes" and then how many times Q2 has been answered "yes", then how many times Q1 has been answered "Yes" and Q2 has been answered "no" and so on. As I add more and more fields so the matrix becomes more complex and does not give me what I want.
All I want is "Q1 has these outcomes", "Q2 has these outcomes", and I am ending up with a response with numerous permutations on it. (hope this all makes sense)

This is what I want for each question



This what I am getting when I add other columns to the query and this is only for 2 of the 54 fields.






How can I simply get the totals for each individuals question without creating a query for each question and then probably creating a sub report in the overall report that goes back to the team leaders for review?
Do I need to re-arrange the data? I thought about assigning numerical values to each outcome, but really need a count of Yes, No, N/A. For example as an insurance company if there is no injury to a claimant then several questions would be N/A so I can't ignore them as we need to know if a handler has missed the injury out entirely if it should have been applicable.
I can see a way around this but it means creating a separate query for every question and that’s not somewhere I really want to go.
Any help would be greatly appreciated.

Tim

[alansidman]

I believe that you have a non normalized table and that is giving you issue.

Instead of setting up a new text field for each question, you should have set up a field for question number and a field for response. Then you would be able to aggregate on the questions and the responses.

Look at this link on normalization and database set up.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[TimMoffy]

Thanks for your response
I have read the paper you directed me to and I think (?) I have set the tables up the right way.
I have a table with tw ofuields in it, tblAuditQuestions, where the question number is the Primary key.
Then in the table datastore i have a unique field for each question and the outcome recorded from a combo box list of Yes, No, N/A.
There isn't a relationship between these two tables as i'm not sure how you would do that given that there isnt a single Question number field in the DataStore table.
if it helps each question has two fields associated with it in the Datastore, that is the outcomes we have mentioned and a memo field for recording any commentstha tneed to be reported back again. That's pretty much it, apaprt from date of audit and handler name.

Not really sure what to do next. If the answer is in the paper you directed me to then i have missed it and/or dont understand it and might have to resport to Plan A, which will be like a swan swiming on the water , looks good to the user and my boss, but the reality is that it will be horrible behind it with numerous queries and sub reports. Not looking forward to that, especially if they ever change the number of questions!

Thanks again for your help.