Variance Calculation

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

Guest

I have a query where I want the variance between amount fields in two
different tables. It calculateds the variance if both fields have values but
not when one of the fields are empty. I have created one of the tables I am
using from a query that summarized the values in one line. Is this the
problem?
 
That depends on what values you consider legitimate.

If you consider no-value to be legitimately equal to "0", you can still
compare between your two fields. You can use the Nz() function to convert
no-values (i.e., nulls) to zeros.

But if your situation can produce no-values which mean there's nothing there
to compare, you DON'T want to do the calculation between "nothing
meaningful" and some value.

Regards

Jeff Boyce
<Office/Access MVP>
 
Jeff,

Using the Nz() function the fields are populated with zeros but it is still
not doing the calculation if one field has values in it. For example:

Actual Amount Budget Amount Variance
100.00 50.00 50.00
0 25.00
25.00 0

There are blanks in any variance field that does not have an amount greater
or less than zero in the actual or budget field.

Thanks,

Larry
 
Without a look at the actual calculation you are doing, it will be tough to
figure out what isn't working...

Jeff Boyce
<Office/Access MVP>
 
Don't know if this would help or if you have to look at the properties of
each field.

SELECT CCCEPeriod.Period, CCCEPeriod.CostCenter, CCCEPeriod.[Cost Element],
Nz([TGL2005Budget]![Amount],0) AS [Actual Amount],
nz([TGLSumbyAcct]![Amount],0) AS [Budget Amount],
nz([TGL2005Budget]![Amount]-[TGLSumbyAcct]![Amount],0) AS Variance
FROM (CCCEPeriod LEFT JOIN TGL2005Budget ON (CCCEPeriod.Period =
TGL2005Budget.Period) AND (CCCEPeriod.[Cost Element] = TGL2005Budget.[Cost
Element]) AND (CCCEPeriod.CostCenter = TGL2005Budget.CostCenter)) LEFT JOIN
TGLSumbyAcct ON (CCCEPeriod.Period = TGLSumbyAcct.Period) AND
(CCCEPeriod.[Cost Element] = TGLSumbyAcct.[Cost Element]) AND
(CCCEPeriod.CostCenter = TGLSumbyAcct.CostCenter)
WHERE (((CCCEPeriod.Period)=10) AND ((CCCEPeriod.CostCenter)=598043) AND
((CCCEPeriod.[Cost Element])=51000004 Or (CCCEPeriod.[Cost
Element])>=60000000));
 
Pardon me, but just in case Ken is busy,

You need to use the nz INSIDE the calculation

Nz([TGL2005Budget]![Amount],0)-Nz([TGLSumbyAcct]![Amount],0) AS Variance

lpdc said:
Don't know if this would help or if you have to look at the properties of
each field.

SELECT CCCEPeriod.Period, CCCEPeriod.CostCenter, CCCEPeriod.[Cost
Element],
Nz([TGL2005Budget]![Amount],0) AS [Actual Amount],
nz([TGLSumbyAcct]![Amount],0) AS [Budget Amount],
nz([TGL2005Budget]![Amount]-[TGLSumbyAcct]![Amount],0) AS Variance
FROM (CCCEPeriod LEFT JOIN TGL2005Budget ON (CCCEPeriod.Period =
TGL2005Budget.Period) AND (CCCEPeriod.[Cost Element] = TGL2005Budget.[Cost
Element]) AND (CCCEPeriod.CostCenter = TGL2005Budget.CostCenter)) LEFT
JOIN
TGLSumbyAcct ON (CCCEPeriod.Period = TGLSumbyAcct.Period) AND
(CCCEPeriod.[Cost Element] = TGLSumbyAcct.[Cost Element]) AND
(CCCEPeriod.CostCenter = TGLSumbyAcct.CostCenter)
WHERE (((CCCEPeriod.Period)=10) AND ((CCCEPeriod.CostCenter)=598043) AND
((CCCEPeriod.[Cost Element])=51000004 Or (CCCEPeriod.[Cost
Element])>=60000000));


Jeff Boyce said:
Without a look at the actual calculation you are doing, it will be tough
to
figure out what isn't working...

Jeff Boyce
<Office/Access MVP>
Click to expand...
Click to expand...
 
What happens if you put the Nz() function inside the calculation, rather
than wrapping the subtraction with it?

Jeff Boyce
<Office/Access MVP>

lpdc said:
Don't know if this would help or if you have to look at the properties of
each field.

SELECT CCCEPeriod.Period, CCCEPeriod.CostCenter, CCCEPeriod.[Cost
Element],
Nz([TGL2005Budget]![Amount],0) AS [Actual Amount],
nz([TGLSumbyAcct]![Amount],0) AS [Budget Amount],
nz([TGL2005Budget]![Amount]-[TGLSumbyAcct]![Amount],0) AS Variance
FROM (CCCEPeriod LEFT JOIN TGL2005Budget ON (CCCEPeriod.Period =
TGL2005Budget.Period) AND (CCCEPeriod.[Cost Element] = TGL2005Budget.[Cost
Element]) AND (CCCEPeriod.CostCenter = TGL2005Budget.CostCenter)) LEFT
JOIN
TGLSumbyAcct ON (CCCEPeriod.Period = TGLSumbyAcct.Period) AND
(CCCEPeriod.[Cost Element] = TGLSumbyAcct.[Cost Element]) AND
(CCCEPeriod.CostCenter = TGLSumbyAcct.CostCenter)
WHERE (((CCCEPeriod.Period)=10) AND ((CCCEPeriod.CostCenter)=598043) AND
((CCCEPeriod.[Cost Element])=51000004 Or (CCCEPeriod.[Cost
Element])>=60000000));


Jeff Boyce said:
Without a look at the actual calculation you are doing, it will be tough
to
figure out what isn't working...

Jeff Boyce
<Office/Access MVP>
Click to expand...
Click to expand...
 
I did and it worked. Thanks so much for yout help.

Jeff Boyce said:
What happens if you put the Nz() function inside the calculation, rather
than wrapping the subtraction with it?

Jeff Boyce
<Office/Access MVP>

lpdc said:
Don't know if this would help or if you have to look at the properties of
each field.

SELECT CCCEPeriod.Period, CCCEPeriod.CostCenter, CCCEPeriod.[Cost
Element],
Nz([TGL2005Budget]![Amount],0) AS [Actual Amount],
nz([TGLSumbyAcct]![Amount],0) AS [Budget Amount],
nz([TGL2005Budget]![Amount]-[TGLSumbyAcct]![Amount],0) AS Variance
FROM (CCCEPeriod LEFT JOIN TGL2005Budget ON (CCCEPeriod.Period =
TGL2005Budget.Period) AND (CCCEPeriod.[Cost Element] = TGL2005Budget.[Cost
Element]) AND (CCCEPeriod.CostCenter = TGL2005Budget.CostCenter)) LEFT
JOIN
TGLSumbyAcct ON (CCCEPeriod.Period = TGLSumbyAcct.Period) AND
(CCCEPeriod.[Cost Element] = TGLSumbyAcct.[Cost Element]) AND
(CCCEPeriod.CostCenter = TGLSumbyAcct.CostCenter)
WHERE (((CCCEPeriod.Period)=10) AND ((CCCEPeriod.CostCenter)=598043) AND
((CCCEPeriod.[Cost Element])=51000004 Or (CCCEPeriod.[Cost
Element])>=60000000));


Jeff Boyce said:
Without a look at the actual calculation you are doing, it will be tough
to
figure out what isn't working...

Jeff Boyce
<Office/Access MVP>

Jeff,

Using the Nz() function the fields are populated with zeros but it is
still
not doing the calculation if one field has values in it. For example:

Actual Amount Budget Amount Variance
100.00 50.00
50.00
0 25.00
25.00 0

There are blanks in any variance field that does not have an amount
greater
or less than zero in the actual or budget field.

Thanks,

Larry
:

That depends on what values you consider legitimate.

If you consider no-value to be legitimately equal to "0", you can
still
compare between your two fields. You can use the Nz() function to
convert
no-values (i.e., nulls) to zeros.

But if your situation can produce no-values which mean there's nothing
there
to compare, you DON'T want to do the calculation between "nothing
meaningful" and some value.

Regards

Jeff Boyce
<Office/Access MVP>

I have a query where I want the variance between amount fields in two
different tables. It calculateds the variance if both fields have
values
but
not when one of the fields are empty. I have created one of the
tables
I
am
using from a query that summarized the values in one line. Is this
the
problem?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top