Table design to enable desired report with sums.

[mandy_frog]

I have a database that will print billling statements based on a project. A
project has two types of expenses; employee hours and purchases. I would
like a report that looks something like this:
Project Name
Employee Hours
01/05/02 Jane Doe Filing 5 $15.60
02/05/02 Bob Smith Reporting 8 $160.34
etc.
Employee Hours Total $xxx.xx
Purchases
02/03/02 Paper Needed for Reports $23.89
02/07/02 Postage Mailing $37.00
etc.
Purchases Total $xxx.xx
Bill Total
$xxx.xx
Currently i have a number of tables; one for project details, one for
purchases and one for employee hours. I had this working until i had to add
the purchase information. I can not figure how to get the information from
two tables onto this report, and summing. If i placed all the purchase and
hours information in one table there would be a number of empty fields in
each row, plus the chance for a user to enter a purchase and hour on the same
line. The closest i have come is to have a report with two subforms on it.
But i can't get the bill total figured that way. Any help would be greatly
appreciated. I have tried everything i can thing of.

 

[Duane Hookom]

Consider using a union query
SELECT ProjectiID, "Employee Hours" as TheType, WrkDate as TheDate, EmpName
as Item, Description, Hours as Qty, Whatever as Cost
FROM tblEmpHours
UNION ALL
SELECT ProjectID, "Purchases", PurchDate, PurchItem, Description, 1,
Whatever
FROM tblPurchases;

You can then use this union query for your report and Sort and Group on
Project and "TheType".

 

[mandy_frog]

Where do i enter this?

Also, i have translated your sample code into the code actually matching my
tables. Does the syntax look OK? Can i calculate in the query

Thanks so much!!

SELECT ProjectID, "Employee Hours" as Type, Date as Date, EmployeeeID as
Item, ActivityID as Description, Billing Rate as Cost, Hours as Units,
[Billing Rate] * [Hours]as Total Cost
FROM tblHours
UNION ALL
SELECT ProjectID, "Purchase" as Type, Date as Date, Equipment as Item,
Description as Description, Cost as Cost, Units as Units, [Cost] * [Units] as
Total Cost
FROM tblPurchase

--amanda

 

[Duane Hookom]

This should be the SQL view of your query. Note that I changed your [Date]
field to ItemDate since Date is the name of a function in VBA. Also, you
don't need to provide "alias" names in the second SELECT. Any field names
with spaces must be wrapped in []s. Both selects have the same number of
columns which is required.

SELECT ProjectID, "Employee Hours" as Type, [Date] as ItemDate,
EmployeeeID as Item, ActivityID as Description, [Billing Rate] as Cost,
Hours as Units, [Billing Rate] * [Hours] as TotalCost
FROM tblHours
UNION ALL
SELECT ProjectID, "Purchase", [Date],
Equipment, Description, Cost,
Units, [Cost] * [Units]
FROM tblPurchase;