How to make a one report which calls multiple SQL querries

[Jacko7289]

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

[abolos]

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

[Jacko7289]

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

[MMcCarthy]

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.

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

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.