Wrong results from totals query

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
 
D

Duane Hookom

Did you notice that each of the TotalBudgetHours you are getting are
multiples of what you expect? Since Budgeted Hours is the 1 ()side of the 1
to many, you might want to use Avg(B.BudgetedHours) rather than Sum().

--
Duane Hookom
MS Access MVP


Larry said:
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
Click to expand...
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,
Click to expand...
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.
 
D

Dale Fye

Larry,

What exactly are you trying to do here? It looks like what you are trying to do, is determine the number of hours used during a particular time period, against the total number of hours budgeted for each job. If you have multiple records in the [Budgeted Hours] table for a particular job, then each of those records will join to each of the records in the Hours Used table, which will significantly increase the number of hours.

Try converting it to:

SELECT B.JobNumber
, B.SumOfBudgetedHours
, H.SumOfHoursUsed
FROM
(SELECT B1.JobNumber
, Sum(B1.BudgetedHours) as SumOfBudgetedHours
FROM [Budgeted Hours] B1
GROUP BY B1.JobNumber) as B
LEFT JOIN
(SELECT H1.JobNumber
, SUM(H1.Hours) as SumOfHoursUsed
FROM [Hours Used] H1
WHERE H1.Date BETWEEN #8/1/2003# And #8/31/2003#
GROUP BY H1.JobNumber) as H
ON B.JobNumber = H.JobNumber

The first subquery determines the total number of hours budgeted for each job.
The second subquery determines the total number of hours used for each job during the period specified
The LEFT JOIN combines the two subqueries to give you the recordset I think you want. A list of all the jobs, the total number of hours budgeted for them, and the number of hours logged against those jobs during August. If this is not what you are looking for, post back with a better explaination of what you are trying to accomplish, not how you are trying to accomplish it. If you are only interested in those jobs, that had hours logged against them during August, change the join to an INNER JOIN.

Dale Fye
Developing Solutions


Larry said:
Have a query with the following SQL statement:
Click to expand...
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:
 
G

Guest

Duane,

You hit it square on. I do have multiple instances of the same JobNumber in the B table.
 
G

Guest

Dale,

This is what I'm trying to do:

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

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

In both tables, the same [JobNumber] may appear multiple times.

Larry
 
G

Guest

Got it!!! Used the following:

SELECT B.JobNumber, B.SumOfBudgetedHours, H.SumOfHoursUsed
FROM [SELECT B.JobNumber, Sum(B.BudgetedHours) as SumOfBudgetedHours
FROM B
GROUP BY B.JobNumber]. AS B LEFT JOIN [SELECT H.JobNumber, SUM(H.Hours) as SumOfHoursUsed
FROM H
WHERE H.Date BETWEEN #8/1/2003# And #8/31/2003#
GROUP BY H.JobNumber]. AS H ON B.JobNumber = H.JobNumber;

many thanks Dale...
 
J

John Spencer (MVP)

My apologies for giving you a bad steer in my original attempt to solve your
problem. Glad you got it worked out.
 
Top