Query not calculating correctly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is my calcuation

Vac:
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0)))

And the SQL View

SELECT XXSUBqrySubstituteLeaveStatusVac.chrSSN,
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0))) AS Vac
FROM XXSUBqrySubstituteLeaveStatusVac LEFT JOIN
XXSUBqrySubstituteLeaveStatusVacADJ ON
(XXSUBqrySubstituteLeaveStatusVac.dtePayEndDate =
XXSUBqrySubstituteLeaveStatusVacADJ.dteTimeStamp) AND
(XXSUBqrySubstituteLeaveStatusVac.chrSSN =
XXSUBqrySubstituteLeaveStatusVacADJ.chrSSN)
GROUP BY XXSUBqrySubstituteLeaveStatusVac.chrSSN;


My problem is that there are 1 lines equalling 6 hours from
XXSUBqrySubstituteLeaveStatusVacADJ! that are not being picked up. What am I
doing wrong?

TIA!

Julie
 
jjacob said:
Here is my calcuation

Vac:
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0)))

And the SQL View

SELECT XXSUBqrySubstituteLeaveStatusVac.chrSSN,
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0)))
AS Vac
FROM XXSUBqrySubstituteLeaveStatusVac LEFT JOIN
XXSUBqrySubstituteLeaveStatusVacADJ ON
(XXSUBqrySubstituteLeaveStatusVac.dtePayEndDate =
XXSUBqrySubstituteLeaveStatusVacADJ.dteTimeStamp) AND
(XXSUBqrySubstituteLeaveStatusVac.chrSSN =
XXSUBqrySubstituteLeaveStatusVacADJ.chrSSN)
GROUP BY XXSUBqrySubstituteLeaveStatusVac.chrSSN;


My problem is that there are 1 lines equalling 6 hours from
XXSUBqrySubstituteLeaveStatusVacADJ! that are not being picked up. What
am I
doing wrong?

TIA!

Julie
Click to expand...

Are you sure there is a corresponding record in
XXSUBqrySubstituteLeaveStatusVac for the record in
XXSUBqrySubstituteLeaveStatusVacADJ? What are the data types of the two date
fields you're joining on (dtePayEndDate and dteTimeStamp)? Keep in mind, if
the date fields contain times as well, the times will have to match exactly
also.

Carl Rapson
 
I believe it is a syntax problem. Each element has be surrounded with the Nz
functon.
Incorrect: Nz( X + Y,0) If either is Null, it will return 0 regardless of
the value of the other. That is because Null + anything = Null. So assumne
X = 3 and Y = Null. It first adds 3 + Null which returns Null. Then the Nz
function is applied and 0 is returned.

Correct: Nz(X,0) + Nz(Y,0) The Nz function is applied to X and 3 is
returned. The Nz function is applied to Y and 0 is returned. Then 0 is
added to 3 and 3 is returned.

Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0)+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0))
 
I changed the calculation to read

Vac:
(Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0)))+Sum(NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0))

Both of the date fields were the same format and the same data (no time
included). I am not sure why the above worked tho!

Thanks!

Carl Rapson said:
jjacob said:
Here is my calcuation

Vac:
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0)))

And the SQL View

SELECT XXSUBqrySubstituteLeaveStatusVac.chrSSN,
Sum(nz([XXSUBqrySubstituteLeaveStatusVac!decVacHours],0+NZ([XXSUBqrySubstituteLeaveStatusVacADJ!decVacHours],0)))
AS Vac
FROM XXSUBqrySubstituteLeaveStatusVac LEFT JOIN
XXSUBqrySubstituteLeaveStatusVacADJ ON
(XXSUBqrySubstituteLeaveStatusVac.dtePayEndDate =
XXSUBqrySubstituteLeaveStatusVacADJ.dteTimeStamp) AND
(XXSUBqrySubstituteLeaveStatusVac.chrSSN =
XXSUBqrySubstituteLeaveStatusVacADJ.chrSSN)
GROUP BY XXSUBqrySubstituteLeaveStatusVac.chrSSN;


My problem is that there are 1 lines equalling 6 hours from
XXSUBqrySubstituteLeaveStatusVacADJ! that are not being picked up. What
am I
doing wrong?

TIA!

Julie
Click to expand...

Are you sure there is a corresponding record in
XXSUBqrySubstituteLeaveStatusVac for the record in
XXSUBqrySubstituteLeaveStatusVacADJ? What are the data types of the two date
fields you're joining on (dtePayEndDate and dteTimeStamp)? Keep in mind, if
the date fields contain times as well, the times will have to match exactly
also.

Carl Rapson
Click to expand...
 
Back
Top