Count and Grouping Question
Posted on 2006-06-07
This is probably the first of two questions, but perhaps I can figure out the second if I get this one.
I will have a one Join in this query - and that I can get just fine (I think).
I am not sure of the best way to get the output I need. Using ColdFusion 7 and Access 2000.
recordID, sessionID, SkillCode, preCheck, Type, postCheck
recordID is unique, sessionID is not unique, SkillCode is not unique, The rest have values 0-3 as a choice
I need to produce output that looks something like this:
SkillCode | preCheck-1| preCheck-2| preCheck-3 | Type-1 | Type-2 | Type-3 | postCheck-1 | postCheck-2 | postCheck-3
100 4 5 2 4 3 11 7 12 5
101 5 12 4 4 10 3 13 3
102 2 4 7 4 3 2 4 13 6
103 1 6 2 13 1 8 18 9
Where each of the values is the total number of each selection within that sessionID.
So - for skill 100 4 people had a preCheck rating of 1, 5 of 2 and 2 of 3 -- 4 had a Type of 1, 3 of 2 and 11 of 3 etc etc
There could be some that will be 0, or none of that rating selected for that skill in that session.
I can seem to pull and count these numbers in seperate queries (these are counts by rating - nothing is summed) and I did manage to pull what I think are all counts in a large table grouped by one of the criteria. What I cant seem to do is figure out the combination of SQL and COldFusion that will allow the display to be grouped as above.
I recieved an answer for a (I thought) similar question some time back - but they seem to be differnt enough that I cant seem to make the transition in thinking to get this done.
Any ideas or reference would be appreciated - and I am happy to supply any information I overlooked.
PS - I assigned the points on the degree of difficulty to me, so if its really simple or very difficult I aplogize