Query with two Tables with date filed for Date Range


R

Rag742

How do I create a Query using two tables that have dates field to
calculate a value using both the tables for a Date Range. For Example

I have four TABLES- Time Card(TC), PROJECT, TC hours(HOURS), and TC
expenses(EXPENSE). TC contains name of employees and their billing
rates. PROJECT contains Name of Project. HOURS field are TCId,
PROJECTId, Dateworked, and Hours. EXPENSE contains fields TCId,
PROJECTId, Dateexpense, Expense.
I want to build a query that over a Date Range that sums billable
amount=hours worked* billing rate+ expense.
I set the Dateworked >=[forms]![Report Date Range]![BeginDate] And
<=[forms]![Report Date Range]![EndDate]

What do I do with the Dateexpense to set the Date Range.
Some one suggested inner join. But this creates another problem. If
there is no value for a
date in expense or hour field for the dates in Range the formula of
total bill is blank. How do i get around this
 
Ad

Advertisements

V

Van T. Dinh

It sounds to me that you need a Union Query being used as the SubQuery
before totalling the ChargeableHours component and the Expense component:

The SQL should be something like (***untested***)

********
SELECT [Combined].ProjectID, Sum([Combined].[ChargeComponent])

FROM
(
SELECT H.ProjectID, H.DateWorked, H.[Hours] * TC.Rates As
[ChargeComponent]
FROM [Hours] AS H INNER JOIN TC ON H.TCID = TC.TCID

UNION ALL

SELECT E.ProjectID, E.ExpenseDate, E.Expense
FROM Expense AS E
) AS [Combined]

WHERE [Combined].[DateWorked] BETWEEN ... AND ...
GROUP BY [Combined].ProjectID
********
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top