By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,943 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,943 IT Pros & Developers. It's quick & easy.

How to make a one report which calls multiple SQL querries

P: 10
Hi,
It's me again :-)
Just wondering if its possible to create one report using the output of two or more SQL Querries.
Currently i've got two SQL querries. one generates the total number of graduate students for the year (SQLGrad) and the other number of graduate students per course per year (SQLGradcourse). Now, what i want to happen (if possible) is that once i clicked the report button it will generate in just one page both the results of the two querries.
Is this possible? Any help on this matter is greatly appreciated.
Cheers,
Jacko7289
Jul 30 '07 #1
Share this Question
Share on Google+
3 Replies


P: 65
Hi,
It's me again :-)
Just wondering if its possible to create one report using the output of two or more SQL Querries.
Currently i've got two SQL querries. one generates the total number of graduate students for the year (SQLGrad) and the other number of graduate students per course per year (SQLGradcourse). Now, what i want to happen (if possible) is that once i clicked the report button it will generate in just one page both the results of the two querries.
Is this possible? Any help on this matter is greatly appreciated.
Cheers,
Jacko7289

You can create a sub report in the main report. This is just an idea between much more. But to me I find it fine.
Abolos
Jul 30 '07 #2

P: 10
I can't make it (Creating sub-report) work using the report design wizard . I got this essage..."You have chosen fields from record sources which the wizard can't connect".
Anyway here are my SQL's which i want to be reported in just one page.

SQL 1
Select DISTINCT CompletionYear, COUNT(*)
From Student
Where CompletionYear in ('2004','2005','2006','2007')
Group By CompletionYear;

SQL 2
Select DISTINCT Course, COUNT(*)
From Student
Where CompletionYear in ('2004','2005','2006','2007')
Group By Course;

Maybe i don't do it correctly. Any suggestion woul be greatly appreciated.
Cheers,
Jacko7289
Aug 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly you will need to return and group by CompletionYear in the second query as well if you want the number of students per course, per year.

Expand|Select|Wrap|Line Numbers
  1. Select DISTINCT Course, COUNT(*), CompletionYear
  2. From Student 
  3. Where CompletionYear in ('2004','2005','2006','2007')
  4. Group By Course, CompletionYear;
  5.  
Also try running both queries in the query window to make sure they are working properly.

Now create a report based on the first query. Create a group by level for CompletionYear

Now when you create the subreport (in the detail section) using the wizard make sure you join on CompletionYear.
Aug 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.