I have a main table that has 6 fields of different status. i.e. msiii, msiv, intern, 2year,3year,4year.
I have a table that has 8 fields, each with a possible number of 1 to 8 for ranking a class. I need to sum the numbers for each ranking based on each status. Like...
msiii - 3 number 1's msiii- 2 number 2's msiii - 5 number 3's etc.
intern - 2 number 2's intern 12 number 4's etc.
I would then calculate percentages based on total of each number entered and chart the result.
Is there a way to query the first part in a single query? I currently can pull sums for each number based on each status in individual queries but that would require 8 queries each for all 6 status categories or 48 queries. My current query looks like this for each number...
SELECT Sum([problemconference]=1) AS probcon, Sum([morningreport]=1) AS mornrpt, Sum([weeklyattending]=1) AS weekattend, Sum([attendinglectures]=1) AS attendlect, Sum([grandrounds]=1) AS grndrnds, Sum([pedsstudy]=1) AS pedsstdy, Sum([amattendingrounds]=1) AS attendrnds, Sum([morbidity]=1) AS morbid
This by the way returns the correct sum for each status category but it is in the form of a negative number with a minus sign in front. Why is that?