Strange Variance Calc in Query

[jessabridge]

I have 2 queries (let's call them A & B) base on two seperate Tables
I have a 3rd query (C) based on a third table (same info as A & B combined.

I have a 4th query so I can compare the Overtime info between the two queries.

I used the #3 join like this.

soc sec # in A joined to the soc sec # in C w a Join # 3
soc sec # in B joined to the soc sec # in C w a Join # 3.

All info and names are there. Even if there is a zero value in A.

I have this formula: Increase / Decrease FY 2012 Time && Half OT Hrs: Sum([Current FY 2012 Time && Half OT Hrs]-[Last FY 2012 Time && Half OT Hrs])

to compare the the #'s from A ([Current FY 2012 Time && Half OT Hrs]) and B ([Last FY 2012 Time && Half OT Hrs]).

My problem is that when I did Straight Time: Increase / Decrease FY 2012 Straight OT Hrs: Sum([Current FY 2012 Straight OT Hrs]-[Last FY 2012 Straight OT Hrs]) everything worked. 4 names. 1 zero value w the correct calculation.

When I Did Time & Half, 62 names with about 8 zero values in A, about 10 in B. 1 record made a blank space in The A coulmn and did not do the Increase/Decrease amount. Another record made a blank space in The B coulmn and did not do the Increase/Decrease amount.

Why would it do the calculation for the other 60 but not those 2?

Any Ideas? I'm stumped...
Jessa

 

[jdraw]

I suggest you show some of the specifics - the table design, the queries
and/or the related SQL.

 

[asgard]

I have 2 queries (let's call them A & B) base on two seperate Tables
I have a 3rd query (C) based on a third table (same info as A & B combined.

I have a 4th query so I can compare the Overtime info between the two queries.

I used the #3 join like this.

soc sec # in A joined to the soc sec # in C w a Join # 3
soc sec # in B joined to the soc sec # in C w a Join # 3.

All info and names are there. Even if there is a zero value in A.

I have this formula: Increase / Decrease FY 2012 Time && Half OT Hrs: Sum([Current FY 2012 Time && Half OT Hrs]-[Last FY 2012 Time && Half OT Hrs])

to compare the the #'s from A ([Current FY 2012 Time && Half OT Hrs]) and B ([Last FY 2012 Time && Half OT Hrs]).

My problem is that when I did Straight Time: Increase / Decrease FY 2012 Straight OT Hrs: Sum([Current FY 2012 Straight OT Hrs]-[Last FY 2012 Straight OT Hrs]) everything worked. 4 names. 1 zero value w the correct calculation.

When I Did Time & Half, 62 names with about 8 zero values in A, about 10 in B. 1 record made a blank space in The A coulmn and did not do the Increase/Decrease amount. Another record made a blank space in The B coulmn and did not do the Increase/Decrease amount.

Why would it do the calculation for the other 60 but not those 2?

Any Ideas? I'm stumped...
Jessa

Is there a way you can link a picture or link?

 

[jessabridge]

I figured it out. Thank you all!
Increase / Decrease FY 2012 Time && Half OT Hrs: Sum(nz([Current FY 2012 Time && Half OT Hrs],0)-nz([Last FY 2012 Time && Half OT Hrs],0))