Sums from two tables

[Guest]

I have two tables: [Budgeted Hours] and [Hours Used].
[Budgeted Hours] has two fields: [Job Number] and [BudgetedHours]
[Hours Used] has three fields: [Job Number], [Hours] and [Date]
Both tables are related though the [Job Number] field.
I would like to create one query that gives me:

The [Job Number] in the first column in ascending order.
The sum of the hours budgeted for that [Job Number] in the second colum
The sum of the hours used for that [Job Number] between two dates in the third column.

I can do this with two queries but this seems very inefficient. I would really like to get this into one query. Can anyone help?

Thanks,
Larry

 

[Bill Murphy]

I've done lots of queries similar to this, and I believe yours does require
two queries. The first would sum the actual hours in Hours Used, grouping
on the Job Number. The second query would use the Budgeted Hours table and
the first summary query to line up budgeted versus actual by Job Number. In
the second query you could also have a computed column that would show the
difference between budget and actual.

Bill

I have two tables: [Budgeted Hours] and [Hours Used].
[Budgeted Hours] has two fields: [Job Number] and [BudgetedHours]
[Hours Used] has three fields: [Job Number], [Hours] and [Date]
Both tables are related though the [Job Number] field.
I would like to create one query that gives me:

The [Job Number] in the first column in ascending order.
The sum of the hours budgeted for that [Job Number] in the second colum
The sum of the hours used for that [Job Number] between two dates in the third column.

I can do this with two queries but this seems very inefficient. I would

really like to get this into one query. Can anyone help?

 

[Guest]

Since I will be having a report based on the results, I had thought that I might be able to do a subquery of some kind to get around have two separate queries.

 

[John Spencer (MVP)]

Looks like a simple Totals query with a join.


SELECT B.JobNumber,
Sum(B.BudgetedHours) as TotalBudgetHours,
Sum(H.Hours) as HoursUsed
FROM [Budgeted Hours] as B LEFT JOIN [Hours Used] as H
ON B.JobNumber = H.JobNumber
WHERE H.[Date] Between #1/1/03# AND #1/31/03# OR H.Date is Null
GROUP BY B.JobNumber
ORDER BY B.JobNumber

If you want only the JobNumbers that had hours used, then modify the query to
change the LEFT JOIN to an INNER JOIN and drop "OR H.Date Is Null" in the where clause.

 

[Guest]

John,

Thanks, I'll give it a try..

 

[Guest]

John,

Pasted your SQl into the query (changed names of tables to B and H respectively). Get error saying [Budgeted Hours] table or query doesn't exist. Suspect same would happen with [Hours Used] since a table or query bby taht name doesn't exist either. Am I missing something?

Thanks

Larry

 

[Guest]

John,

Got it working, realized what I did wrong.. Many thanks for the help. You MVP people are worth yor weight in gold!

Regards

larry