troubleshooting Question

Possible to consolidate 48 small queries into one or at least fewer queries?

Avatar of leachj
leachj asked on
Microsoft AccessSQL
11 Comments1 Solution233 ViewsLast Modified:
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
FROM tblRanking;

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?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros