Avatar of leachj
leachj

asked on 

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

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?

Thanks,
John
Microsoft AccessSQL

Avatar of undefined
Last Comment
leachj
Avatar of timeshell
timeshell

Create a view with a UNION command for each query.  That way you can query the one view to return the results of all.

For example:

Select field1, field2, field3 from table 1
union all
select field1, field2, field3 from table 2
Avatar of leachj
leachj

ASKER

Can I do this through the query design or is it entered manually somewhere?

John
Avatar of timeshell
timeshell

A query designer may not be able to display this graphically.  You would probably have to create a view and its SQL syntax manually.
Avatar of puppydogbuddy
puppydogbuddy

I think the crosstab query wizard will work for you.  First, create a single base query to replace the 8 individual queries to something like this:
"Select class, status, rank from YourTable"  .............or even, "Select * From YourTable"
Save your base query, then select the crosstab query wizard and use this as the source for your crosstab.  Answer the applicable wizard prompts as follows:
ColumnHeader -----> status
Value  ---------------> rank
RowHeader --------> class, other unused columns
Will explain how to get totals later....if you want to pursue
Avatar of leachj
leachj

ASKER

The cross tab query will only allow 3 fields for row headings.  I did a query using my main table which contains the status, and the ranking table which contains the rankings.  There are 8 ranking categories.  If I choose status as one row header, it leaves me with only 2 of the rankings.  Am I misunderstanding this?  Here is the query....

SELECT tblMain.Status, tblRanking.ProblemConference, tblRanking.MorningReport, tblRanking.WeeklyAttending, tblRanking.AttendingLectures, tblRanking.GrandRounds, tblRanking.PedsStudy, tblRanking.AMattendingRounds, tblRanking.Morbidity
FROM tblMain INNER JOIN tblRanking ON tblMain.SessionID = tblRanking.SessionID;

The sessionid is the key field for record identification.

John
Avatar of puppydogbuddy
puppydogbuddy

Don't assume that you are limited to 3 rows just because that is all that the wizard prompts you for.  After you create the initial crosstab, you can place it in design view and drag as many row headers (within reason) as you need.

status was supposed to be a column header, but it can be both....just  place the query in design view and drag status to the field row again.....so that you have status twice, once as a row header and once as the column header.  Does that fix your problem?  If not, please show me a sample of what the output from your query looks like.
Avatar of leachj
leachj

ASKER

After much wailing and gnashing of teeth, I don't think I can do it in one shot.  The best I can come up with is 8 of these simple queries...

SELECT Query1.Status, Query1.MorningReport, Count(Query1.MorningReport) AS CountOfMorningReport
FROM Query1
GROUP BY Query1.Status, Query1.MorningReport;

One for each section.  If I import these queries into excel and do a pivot table I can get the result I want.  A long way around!  I tried doing pivot table in access but get error message... " cannot instantiate oledbsimpleprovider component".  I started another thread for that one.

John
Avatar of puppydogbuddy
puppydogbuddy

You have not provided a sample of your output, so I am not sure.  However, if each of the Categories (see below) has a separate column in  the ranking table because each of them can have a status in each  session, then I am in agreement with you.  However, if only one of the categories can have a status in any session,  I still think you couldn use a crosstab query built on a base query that looks something like this (air code):
Select sessionID, status,  rank, Category From (Select tblRanking.ProblemConference, tblRanking.MorningReport, tblRanking.WeeklyAttending, tblRanking.AttendingLectures, tblRanking.GrandRounds, tblRanking.PedsStudy, tblRanking.AMattendingRounds, tblRanking.Morbidity) As Category From tblRanking)
Avatar of leachj
leachj

ASKER

Well, I'm still beating my head against the wall on this.  I have reduced it to 8 queries.  Each one gives me the totals of the number selected from each category based on each of the 6 status'.  These are based on a query to pull info from 2 tables..... It tells me what numbers were entered for each category by each status.

SELECT tblMain.Status, tblRanking.ProblemConference, tblRanking.MorningReport, tblRanking.WeeklyAttending, tblRanking.AttendingLectures, tblRanking.GrandRounds, tblRanking.PedsStudy, tblRanking.AMattendingRounds, tblRanking.Morbidity
FROM tblMain INNER JOIN tblRanking ON tblMain.SessionID = tblRanking.SessionID;

For each record, the category (2year, 3year, Intern etc), selected the number stored.
The output looks like this I sorted them for clarity...

Status      ProblemConference  MorningReport  WeeklyAttending  AttendingLectures  GrandRounds  PedsStudy  AMattendingRounds  Morbidity
2year            1            3            2            7            6            4            5      
2year            7            6            5            4            3            2            1      
2year            1            2            3            4            5            6            7      
2year            1            2            3            4            5            6            7      
2year            4            5            6            3            2            7            1      
3year            1            2            3            4            5            6            7            8
3year            8            1            2            7            6            3            4            5
4year            1            2            3            5            7            8            6            4
Intern            7            6            5            4            3            2            1      
Intern            2            4            6            1            3            5            7      

etc.....

Here is one of the 8 queries.....

SELECT Query1.Status, Query1.AttendingLectures AS [Number], Count(Query1.AttendingLectures) AS NumberCount
FROM Query1
GROUP BY Query1.Status, Query1.AttendingLectures
HAVING (((Query1.AttendingLectures) Is Not Null));

The output looks like this.  It counts the selected numbers (2year picked 1 number 3, 3 number 4 and 1 number 7)...

Status      Number      NumberCount
2year         3                 1
2year         4                 3
2year         7                 1
3year         4                 1
3year         7                 1
4year         5                 1
Intern          1                  1
Intern          3                  2
Intern          4                  8
Intern          6                  2
MSIII           3                   1
MSIII           4                   1
MSIII           5                   1
MSIV         1                 1
MSIV         2                 1
MSIV         3                 3
MSIV         4                 2

As I said, there are 8 of these, one for each category.  Is there a way to combine these to get the totals in on fell swoop? i.e. the total of all 1s selected in all categories by each status?

All of the MSIII chose 1 twenty times for all 8 categories.
All of the MSIII chose 2 twelve times for all 8 categories.
etc.....
All of the Intern chose 1 fifteen times for all 8 categories.
All of the intern chose 2 thirty times for all 8 categories.
etc....

Hope this is clear.

Thanks,
John

ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of leachj
leachj

ASKER

I have seen this before, when I was searching for an answer.  I'm not sure I can do this in asp .net2 but may give it a try.

Thanks,
John
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo