Left Joins on Multiple Fields

[kt1978]

All

I have attached a small sample to explain what I am trying to do...

Basically, I have a table with lots of Fields, Q1, Q2, Q3, Q4, Q5, Q6 and so on.

There are various codes in those fields.

All I want to do is then have a query with a count of each individual code.

The only way I can think of doing this is by having a query for each Q field and then create another query that joins all the queries, Using LEFT JOINS.

This is the sample I have attached (although on a small sample of fields for ease of understanding). The problem with this is that I would have to do 97 queries in total, the 96 for each Q field and then the one to combine them all.

Can I do this with on Query, or do you have any other suggestions.

Thanks

 

[thingssocomplex]

Why don't you have a table with a field named Question, you then could have each question listed, you can have a second field with Code, a third field for date and so on.

Question Code
Q1 A
Q2 C
Q3 B
Q4 D

 

[kt1978]

Hi

That won't work for my data, they are not questions and there is one record per person. The Q's are 15min intervals throughout the day and the code is the task.

I think I need something like the sample in this link but I can't apply it to my data
http://nm1m.blogspot.com/2007/10/multiple-left-joins-in-ms-access.html

This uses the Multiple LEFT JOIN's or is it UNION Joins.

Hope that makes sense.

Help please.....

 

[kt1978]

Hi all

Can anyone tell me if what I'm trying to do is possible!

I'm sure its got to be something with the multiple left Joins but every time I create them it will show the records from Q1 and then only show the rest of the records for the Q's where there is a value in the Q1.

Thanks in advance...