Multiple Queries on one Report

[Walter Steadman]

Greetings all,
This may be very simple, but I am running in to so many problems with
trying to get information from two queries to show up on a report. I have
created two queries and they work fine stand alone. They look like the
following

Query 1 (in report view) queries all pilots with dates between two dates
then on the report I have it sum the totals of hours so it looks like this:

PilotID LastName FirstName SumofHours_in_Period1
A123 Smith Dave 36
B234 Jones Rick 33
E345 Stone Bob 22


This is correct as it totals up all hours a pilot has flown between two
dates. It only lists the pilots that have hours flown between the two dates
(this causes a problem for the next portion)

The next query is all hours a pilot has flown that are NOT BETWEEN the dates
above. It will look like this

PilotID LastName FirstName SumofHours_in_Period2
A123 Smith Dave 22
B234 Jones Rick 18
C134 Clark Tom 16
D432 Folk Tammy 15


What I was hoping to be able to do was do both queries somehow and get a
report to look like below:

PilotID LastName FirstName SumofHours_in_Period1
SumofHours_in_Period2 TOTAL HOURS
A123 Smith Dave 36
22 58
B234 Jones Rick 33
18 51
C134 Clark Tom 0
16 16
D432 Folk Tammy 0
15 15
E345 Stone Bob 22
0 22

Any ideas on how to make this work? Is it possible?

Thanks in Advance

Wally Steadman
US Army in Iraq

 

[David Lloyd]

Wally:

One approach would be to modify you existing queries slightly, create a
UNION query of the results of these two queries and then do an additional
grouping query to get the results you need. There are three steps here, and
I will detail each step below.

First, I would modify the two existing queries so that they have all of the
fields that you want in your final query.

Query1
PilotID LastName FirstName SumofHours_in_Period1
SumofHours_in_Period2 TOTAL HOURS
A123 Smith Dave 36
0 36
B234 Jones Rick 33
0 33
E345 Stone Bob 22
0 22

Query2
PilotID LastName FirstName SumofHours_in_Period1
SumofHours_in_Period2 TOTAL HOURS
A123 Smith Dave 0
22 22
B234 Jones Rick 0
18 18
C134 Clark Tom 0
16 16
D432 Folk Tammy 0
15 15

Step 2 would be to UNION Query1 and Query2. Step 3 would be to create
another query from your UNION query in which you group on PilotID, LastName,
and First Name, and Sum Period1, Period2, and Total Hours.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Greetings all,
This may be very simple, but I am running in to so many problems with
trying to get information from two queries to show up on a report. I have
created two queries and they work fine stand alone. They look like the
following

Query 1 (in report view) queries all pilots with dates between two dates
then on the report I have it sum the totals of hours so it looks like this:

PilotID LastName FirstName SumofHours_in_Period1
A123 Smith Dave 36
B234 Jones Rick 33
E345 Stone Bob 22


This is correct as it totals up all hours a pilot has flown between two
dates. It only lists the pilots that have hours flown between the two dates
(this causes a problem for the next portion)

The next query is all hours a pilot has flown that are NOT BETWEEN the dates
above. It will look like this

PilotID LastName FirstName SumofHours_in_Period2
A123 Smith Dave 22
B234 Jones Rick 18
C134 Clark Tom 16
D432 Folk Tammy 15


What I was hoping to be able to do was do both queries somehow and get a
report to look like below:

PilotID LastName FirstName SumofHours_in_Period1
SumofHours_in_Period2 TOTAL HOURS
A123 Smith Dave 36
22 58
B234 Jones Rick 33
18 51
C134 Clark Tom 0
16 16
D432 Folk Tammy 0
15 15
E345 Stone Bob 22
0 22

Any ideas on how to make this work? Is it possible?

Thanks in Advance

Wally Steadman
US Army in Iraq