Combining information form two queries into one report.

[Altius]

Access Version: 2003
OS: XP Pro
Sorry if this is a bit long winded. First a little info about the database. This database is used to keep track of checkout information from our production department. It has three main tables that store information, the first one, that I have named "Checkout", stores the information I am querying for the two reports. It has six different fields - OrderNumber, Date, CheckedBy, BuiltBy, SerialNumber, and ComputerType. Using the following query, I pull information for a certain person and date range then group based on the date and computer type, count up the number of that computer type for that date and calculate the hours of work that translates to.

(Query 1)

SELECT Checkout.Date, Checkout.BuiltBy, Checkout.ComputerType,
Count(Checkout.ComputerType) AS CountOfComputerType,
IIf([ComputerType]="Desktop",[CountOfComputerType]*1,
IIf([ComputerType]="Laptop",[CountOfComputerType]*0.5,
IIf([ComputerType]="Server",[CountOfComputerType]*2,
IIf([ComputerType]="MB Kit",[CountOfComputerType]/12)))) AS Hours
FROM Checkout
GROUP BY Checkout.Date, Checkout.BuiltBy, Checkout.ComputerType
HAVING (((Checkout.Date) Between [Forms]![Production Report]![From Date] And [Forms]![Production Report]![To Date]) 
AND ((Checkout.BuiltBy)=[Forms]![Production Report]![TechName]));
 

In the second query, which follows, I select the number of machines they have checked out by using the same date range from above and searching for their initials in the "CheckedBy" field, then count the number of occurrences and calculate the hours of work that translates to.

(Query 2)

SELECT Checkout.Date, Checkout.CheckedBy, Count(Checkout.CheckedBy) AS CountOfCheckedBy, [CountOfCheckedBy]/3 AS Hours
FROM Checkout
GROUP BY Checkout.Date, Checkout.CheckedBy
HAVING (((Checkout.Date) Between [Forms]![Production Report]![From Date] And [Forms]![Production Report]![To Date]) 
AND ((Checkout.CheckedBy)=[Forms]![Production Report]![TechName]));

The problem is that I am not sure how to combine the information from these two queries in one report that will list the total number of calculated hours by day and then put an average for the these days at the bottom of the report.

Anybody have any thought?

[Altius]

It took me two day but I came up with a solution. I had to create four different queries, one main report, and four different subreports.