Putting info from 2 queries into a report

[Guest]

Access Version: 2003

OS: XP Pro

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?

 

[Marshall Barton]

Access Version: 2003

OS: XP Pro

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.

Depends on how you want to display it in the report. If you
want a summary in the report header/footer section, then you
should use a subreport based on the second query.

If you want to sort on the CountOfCheckedBy values, then you
need to create a third query that Joins your two queries om
the CheckedBy and Checkout.Date fields.

Note that you should be using a WHERE clause instead of a
HAVING clause in both of your queries.

 

[Guest]

Thanks Marshall! Your advice worked perfectly.

Access Version: 2003

OS: XP Pro

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.

Depends on how you want to display it in the report. If you
want a summary in the report header/footer section, then you
should use a subreport based on the second query.

If you want to sort on the CountOfCheckedBy values, then you
need to create a third query that Joins your two queries om
the CheckedBy and Checkout.Date fields.

Note that you should be using a WHERE clause instead of a
HAVING clause in both of your queries.