G
Guest
Have a query with the following SQL statement:
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 #8/1/2003# And #8/31/2003# Or (H.Date) Is Null))
GROUP BY B.JobNumber
ORDER BY B.JobNumber;
Although it runs, it returns the wrong answers. Results are grossly over the real numbers. Below is what I should be getting:
JobNumber SumOfBudget SumOfHours
111 20 15
115 453 95
213 701 216.5
214 28 28
312 2143 166
511 252 18
512 2095 734
This is what I'm getting with the SQL above:
JobNumber TotalBudgetHours HoursUsed
111 60 15
115 3171 1710
213 16123 6495
214 112 28
312 27859 1660
511 1008 36
512 64945 4404
Doesn't seem to make sense. I've tried it with right joins, left joins, etc and still get the wrong answers. Note, for each JobNumber in table B, there can be many JobNumber's in table H. All I'm trying to do is get the total number of hours budgeted for a given JobNumber and corelate it to the Number of hours used for that JobNumber.
Thanks... Larry
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 #8/1/2003# And #8/31/2003# Or (H.Date) Is Null))
GROUP BY B.JobNumber
ORDER BY B.JobNumber;
Although it runs, it returns the wrong answers. Results are grossly over the real numbers. Below is what I should be getting:
JobNumber SumOfBudget SumOfHours
111 20 15
115 453 95
213 701 216.5
214 28 28
312 2143 166
511 252 18
512 2095 734
This is what I'm getting with the SQL above:
JobNumber TotalBudgetHours HoursUsed
111 60 15
115 3171 1710
213 16123 6495
214 112 28
312 27859 1660
511 1008 36
512 64945 4404
Doesn't seem to make sense. I've tried it with right joins, left joins, etc and still get the wrong answers. Note, for each JobNumber in table B, there can be many JobNumber's in table H. All I'm trying to do is get the total number of hours budgeted for a given JobNumber and corelate it to the Number of hours used for that JobNumber.
Thanks... Larry