asked on

Count and Grouping Question

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.
THe Table:
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.

Thanks!
tim
PS - I assigned the points on the degree of difficulty to me, so if its really simple or very difficult I aplogize

73Spyder

Lets see here

Select
SkillCode
count(preCheck-1 ) AS preCheck-1 ,
count(preCheck-2 ) AS preCheck-2 ,
count(preCheck-3 ) AS preCheck-3 ,
count(Type-1 ) AS Type-1 ,
count(Type-2 ) AS Type-2 ,
count(Type-3 ) AS Type-3 ,
count(postCheck-1 ) AS postCheck-1 ,
count(postCheck-2 ) AS postCheck-2 ,
count(postCheck-3 ) AS postCheck-3

Group by skillcode

see if this works

73Spyder

oops, forgot the form

Select
SkillCode
count(preCheck-1 ) AS preCheck-1 ,
count(preCheck-2 ) AS preCheck-2 ,
count(preCheck-3 ) AS preCheck-3 ,
count(Type-1 ) AS Type-1 ,
count(Type-2 ) AS Type-2 ,
count(Type-3 ) AS Type-3 ,
count(postCheck-1 ) AS postCheck-1 ,
count(postCheck-2 ) AS postCheck-2 ,
count(postCheck-3 ) AS postCheck-3

FROM TABLENAME

Group by skillcode

timponder

ASKER

You know - I was wondering about something similar but I couldnt find anything about it. Thanks!!

I have a meeting in 10 minutes but I will try this tonight.

Is there a good refernce for Count (aggregates) you know of? I did some searching for information like this but all I found were the basic description's. I can search some more but if you know off the top of your head.

I have another question relating to a similar report to track pre-and post check movement but there is an odd way they want to show it. I will post it as a seperate question later.

Thanks again!
tim

timponder

ASKER

I didnt have much time to work on this - but initially I get the error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

73Spyder

I left out a comma after the first skillcode

Select
SkillCode ,
count(preCheck-1 ) AS preCheck-1 ,
count(preCheck-2 ) AS preCheck-2 ,
count(preCheck-3 ) AS preCheck-3 ,
count(Type-1 ) AS Type-1 ,
count(Type-2 ) AS Type-2 ,
count(Type-3 ) AS Type-3 ,
count(postCheck-1 ) AS postCheck-1 ,
count(postCheck-2 ) AS postCheck-2 ,
count(postCheck-3 ) AS postCheck-3

FROM TABLENAME

Group by skillcode

timponder

ASKER

I had seen that, thanks. For whatever reason it didnt seem to like the dash in the AS clause. When I took that out it stopped complaining.

I still think I am missing something. Is the count(precheck-1) supposed to only count things with a precheck value of 1? I just dont quite understand the output I am getting. All the counts are the same all the way across.
I cant get a nice display to work here but the columns are:

ID LAT_CODE POSTCHECK1 POSTCHECK2 POSTCHECK3 PRECHECK1 PRECHECK2 PRECHECK3 TYPE1 TYPE2 TYPE3
ANd the data is presenting the same count all the way across - which they should not be:

1 A101 13 13 13 13 13 13 13 13 13
2 A102 11 11 11 11 11 11 11 11 11
3 A103 13 13 13 13 13 13 13 13 13
4 A104 17 17 17 17 17 17 17 17 17
5 A105 17 17 17 17 17 17 17 17 17
6 A106 15 15 15 15 15 15 15 15 15

Thank you,
tim

pharmacychoice

Can you possibly send a sample of what the data currently looks like in the database table so that we can try to re0create your issue fully in our own environment? This could help to get you an answer more quickly.

timponder

ASKER

Just in the body of the message as above, or export some of it to a file?

Thanks you!

pharmacychoice

Is the above the data as it sits in the table or your desired way to see it? Your first message said that you need to produce output something like this, so this is where my confusion lies.

timponder

ASKER

Hi -
I see what you are asking. You are correct in that what I asked for is to create that output. They will be counts of each value option that someone selects for a specific Skill.

Here is some data from the table.
The headings are:
LATRecordID      SessionRecordID      Skill      PreCheckRating      Type      PostCheckRating

LATRecordID is just the entry or row number
SessionRecordID ties one persons record together across several tables - and will be slected by a form input - that I can get to work just fine.

The data is:

6730      247      E127      3      0      3
6731      247      E131      2      0      3
6732      247      G001      3      0      4
6733      247      G012      2      1      3
6734      247      G015      3      2      3
6735      247      G016      2      1      3
6736      247      G019      3      2      3
6737      247      G020      2      1      3
6738      247      G027      2      1      0
6739      247      G031      2      1      2
6740      247      G033      2      0      3
6741      247      K006      3      2      3
6742      247      K007      3      2      3
6743      247      K008      3      2      4
6744      247      K009      3      2      4
6745      247      K010      3      2      3
6746      247      K011      3      2      3
6747      248      C310      2      1      4
6748      248      E127      3      2      4
6749      248      E131      3      2      3
6750      248      G001      3      2      3
6751      248      G012      2      1      4
6752      248      G015      2      1      3
6753      248      G016      1      1      4
6754      248      G019      2      1      3
6755      248      G020      1      1      4
6756      248      G027      1      1      4
6757      248      G031      2      1      4
6758      248      G033      3      2      3
6759      248      K006      2      1      4
6760      248      K007      3      2      4
6761      248      K008      3      2      2
6762      248      K009      3      2      4
6763      248      K010      2      2      4
6764      248      K011      3      2      3
6765      249      C310      1      1      2
6766      249      E127      3      2      4
6767      249      E131      3      2      4
6768      249      G001      3      2      4
6769      249      G012      1      1      2
6770      249      G015      2      1      4
6771      249      G016      2      1      3
6772      249      G019      3      2      4
6773      249      G020      2      1      3
6774      249      G027      2      1      4
6775      249      G031      2      1      3
6776      249      G033      3      2      3
6777      249      K006      2      1      4
6778      249      K007      2      1      4
6779      249      K008      3      2      4
6780      249      K009      2      1      4
6781      249      K010      3      2      4
6782      249      K011      3      2      4
6783      251      C310      2      1      3
6784      251      E127      3      2      3
6785      251      E131      2      1      3
6786      251      G001      3      2      3
6787      251      G012      3      2      3
6788      251      G015      3      2      3
6789      251      G016      3      2      3
6790      251      G019      3      2      3
6791      251      G020      2      1      3
6792      251      G027      3      2      3
6793      251      G031      3      2      3
6794      251      G033      3      2      3
6795      251      K006      3      2      3
6796      251      K007      2      1      3
6797      251      K008      3      2      3
6798      251      K009      3      2      3
6799      251      K010      3      2      3
6800      251      K011      3      2      3

Thank you!
tim

timponder

ASKER

Just a bit more explination on how this all will work - I am pretty good at making things confusing by leaving out little bits of information.

Someone will enter a session number into a form. That session number is tied to the SessionRecordID for each individual across several tables. In the report I am after - It will identify all the sessionRecordIDs it needs, and then the output in my initial message will be generated by totaling each of the numeric values assigned to pre and post check and type. So for skill K011 it will run a total for Pre Check value 1, PreCheck value 2, PreCheck value 3 etc etc and the same for Type and PostCheck.

Hope that does not muddy the water -
tim

pharmacychoice

Tim,

That actually clears the water a bit. I am going to need a little while to look at the data and see how this query should be written. Definitley a fun one. I will let you know what I come up with, hopefully a proper solution.

pharmacychoice

This is not the most dynamic thing in the world for this, but it will get you the results that you are seeking:

select skill,
      (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 1 and skill = E1.skill) as [preCheck-1],
      (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 2 and skill = E1.skill) as [preCheck-2],
      (select count(PreCheckRating) from EE_TestTable where PreCheckRating = 3 and skill = E1.skill) as [preCheck-3],
      (select count(Type) from EE_TestTable where Type = 1 and skill = E1.skill) as [Type-1],
      (select count(Type) from EE_TestTable where Type = 2 and skill = E1.skill) as [Type-2],
      (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 1 and skill = E1.skill) as [PostCheck-1],
      (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 2 and skill = E1.skill) as [PostCheck-2],
      (select count(PostCheckRating) from EE_TestTable where PostCheckRating = 3 and skill = E1.skill) as [PostCheck-3]
from TableName E1
group by skill
order by skill

timponder

ASKER

I will check this out as soon as I can - thank you! It may not be the most dynamic, but it beats all the old fashioned looping and counting/adding 1 to previous count I thought I would have to do.

So does the where clause in this act as any where clause would? I didnt realize I could use multiple where's like this.

The next page I need will have some comparisons - but if I can use a where on each line to make this comparison - I think I can get it. If I cant - guess where I will post. :)

Thanks for all of your time and help on this!
tim

ASKER CERTIFIED SOLUTION

pharmacychoice

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garymorin

Sorry not time to look at solution, but look at using pivot tables in Access

pharmacychoice

Tim,

Was this a solution that helped?

timponder

ASKER

Hi,
Thanks pharmacychoice, I got pulled to another piece of this project and just this afternoon ran your suggestion. I need to tweak it with a join and do the output on the page but from just the query compared to the test data it looks right on the mark!

THanks again!
tim

pharmacychoice

Glad to hear it. Good luck!

timponder

ASKER

Pharmacychoice -
I havent posted here much - did you get your points?
Also - I am not sure what I am doing differnt today but something isnt quite the same as my test (could be on the output side).

Just one quick question if you have time:
What in your example is the differnce between EE_Test_Table and Tablemane E1, I must have had it right the other day - but I was on a differnt machine and dont have the code with me.

Thank you again - I know it will work - just got to refocus my thinking.
tim

pharmacychoice

I did get my points. Thank you for that.

In order to do the sub-selects where the table the you are selecting is tha same as the table in the main query, you have to make sure that your sub-select and the main table are aliased differently. In the example that I gave, I aliased the outer table as E1 and left the sub-selects with the full table name so that the sub-select would know that we are joining the data to the main table. I know that this is a little confusing and hard to understand from my wording. If you need more clarification, let me know and I will see what I can do to break it down better.

timponder

ASKER

I have absolutly no idea why that works :) but that is what I must have done differntly. As soon as I changed it - it worked.

Thanks!
tim

timponder

ASKER

Me again -
I have a follow-up question covering a part of this process I thought I could get. You already answered this question - I assume I should just start another topic? I didnt see any real "protocal" for this in the help files.

tim

pharmacychoice

It is generally best to start a new topic because this one is marked as already answered and so the only ones that will see this are people who are still on the list to get emails when questions are asked. I can try to assist with your follow-up if you need me to.

timponder

ASKER

I do :)
I was more worried about being fair with points.

It is question
https://www.experts-exchange.com/questions/21886786/Follow-Up-TO-Grouping-Output-Question.html

tim