Calculation Trouble

  • Thread starter Thread starter RacheleP
  • Start date Start date
R

RacheleP

I need to create a calculation that shows up on a report. I also want the
data to be stored in my table. Should I create the calculation in the query
or the report?

The formula I want to calculate is ([Length]/12)*[Weight per foot]=TotalPcs.
The length and weight per foot data come from different tables. The report
comes from a Select query. Im not sure how to make this work.
 
I need to create a calculation that shows up on a report. I also want the
data to be stored in my table. Should I create the calculation in the query
or the report?

The formula I want to calculate is ([Length]/12)*[Weight per foot]=TotalPcs.
The length and weight per foot data come from different tables. The report
comes from a Select query. Im not sure how to make this work.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox. Simply
use the same expression on the Report as on the Form, or
include the expression as a calculated field in the Report's
Recordsource query.
 
Thanks John. Im still not doing something right though. I typed the
following expression in a text box in my report. When I open the report I
get a syntax error. Any idea what Ive done wrong?

([tblCustPartNumber]![Length]/12)*[tblRGAMaterialInfo]![WeightPerFoot]

Also...should my data be pulling from the tables or from the query that the
report pulls from?


John W. Vinson said:
I need to create a calculation that shows up on a report. I also want the
data to be stored in my table. Should I create the calculation in the query
or the report?

The formula I want to calculate is ([Length]/12)*[Weight per foot]=TotalPcs.
The length and weight per foot data come from different tables. The report
comes from a Select query. Im not sure how to make this work.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox. Simply
use the same expression on the Report as on the Form, or
include the expression as a calculated field in the Report's
Recordsource query.
Click to expand...
 
Thanks John. Im still not doing something right though. I typed the
following expression in a text box in my report. When I open the report I
get a syntax error. Any idea what Ive done wrong?

([tblCustPartNumber]![Length]/12)*[tblRGAMaterialInfo]![WeightPerFoot]

Also...should my data be pulling from the tables or from the query that the
report pulls from?
Click to expand...

The Query should include the tables... doesn't it?? What is the Recordsource
of the report?

The only possible syntax error I see is that you're using ! rather than . for
the delimiters; if the fields are in the Recordsource query you don't need to
qualify them at all (unless you have two fields with the same name in
different tables, which would not be a good idea). Try

ItemWeight: ([Length] / 12) * [WeightPerFoot]

in a vacant Field cell in the query, or

= ([Length] / 12) * [WeightPerFoot]

as the Control Source of a textbox on the report. Note the equal sign.
 
Okay, now my formula returns the correct value but I need to do something
additional. I have two fields, LbsScrapValue and PcsScrapValue. These are
data entry fields in a form. Sometimes I will have the number of pounds and
sometimes I will have the number of pieces. When I have peices it needs to
convert to pounds. If there is a number typed in the PcsScrapValue field I
want the LbsScrapValue field to use the formula to return the conversion to
pounds in that field. But, i also want to be able to type a number into the
LbsScrapValue field and have it return that value. (I have both the data
entry form and the report where this data needs to print pulling from a
query) I used the following argument in the expression builder in the report
but its not working. What did I miss?

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])

P.S. I sure do appreciate your patience and the help you are giving me.

~ Rachele


John W. Vinson said:
Thanks John. Im still not doing something right though. I typed the
following expression in a text box in my report. When I open the report I
get a syntax error. Any idea what Ive done wrong?

([tblCustPartNumber]![Length]/12)*[tblRGAMaterialInfo]![WeightPerFoot]

Also...should my data be pulling from the tables or from the query that the
report pulls from?
Click to expand...

The Query should include the tables... doesn't it?? What is the Recordsource
of the report?

The only possible syntax error I see is that you're using ! rather than . for
the delimiters; if the fields are in the Recordsource query you don't need to
qualify them at all (unless you have two fields with the same name in
different tables, which would not be a good idea). Try

ItemWeight: ([Length] / 12) * [WeightPerFoot]

in a vacant Field cell in the query, or

= ([Length] / 12) * [WeightPerFoot]

as the Control Source of a textbox on the report. Note the equal sign.
Click to expand...
 
Okay, now my formula returns the correct value but I need to do something
additional. I have two fields, LbsScrapValue and PcsScrapValue. These are
data entry fields in a form. Sometimes I will have the number of pounds and
sometimes I will have the number of pieces. When I have peices it needs to
convert to pounds. If there is a number typed in the PcsScrapValue field I
want the LbsScrapValue field to use the formula to return the conversion to
pounds in that field. But, i also want to be able to type a number into the
LbsScrapValue field and have it return that value. (I have both the data
entry form and the report where this data needs to print pulling from a
query) I used the following argument in the expression builder in the report
but its not working. What did I miss?

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])
Click to expand...

Just setting the control source of a textbox on the form will display the
result of the calculation, but not store it in the table. But maybe that's a
good thing!

The proper normalized design would be to store *ONE OR THE OTHER* of
LbsScrapValue and PcsScrpValue, not both. The problem if you store both in the
table is that they could be stored inconsistantly! Are these dollar amounts?
Is it legitimate for the LbsScrapValue to be *different* than this expression?
If not, then you should decide to store just one of them, and routinely
calculate the other one when it's needed.

I have a vague picture of what you're working on, but perhaps a bit of
explanation of your project would help.
P.S. I sure do appreciate your patience and the help you are giving me.
Click to expand...

Like the other volunteers on the group, I just enjoy paying forward some of
the help I've gotten over the years.
 
John,

What I have created is a database to control our returned goods
authorizations. The process starts with issuing an RGA to a customer for
material. That material can be quantified in terms of pounds or pieces,
depending on the customer. My tables simply store either the pieces or
pounds (in separate fields) being returned but my end report needs to state
total pounds regardless of how the customer reported it. Thus the
calculation from pieces to pounds. I abandond storing the converted data
based on your suggestion from your first post. What Im trying to do is in my
report, if there is no value in the LbsScrapValue field it converts the
PcsScrapValue field to pounds (and displays in the LbsScrapValue field. But
if there is a value in the LbsScrapValue field it displays that value.

When I use the formula below everything works fine if Im just converting
piece to pounds.
=(([Length] / 12) * [WeightPerFoot]) * [PcsScrapValue])

But what I want to add is a condition that says if there is a value in the
LbsScrapValue field to display that value but if there is NO value in that
field then to do the calculation from the PcsScrapValue field converting it
to pounds. I thought this was how to write that conditional expression but
its not returning correct values.

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])

I hope this is enough information.

~Rachele


John W. Vinson said:
Okay, now my formula returns the correct value but I need to do something
additional. I have two fields, LbsScrapValue and PcsScrapValue. These are
data entry fields in a form. Sometimes I will have the number of pounds and
sometimes I will have the number of pieces. When I have peices it needs to
convert to pounds. If there is a number typed in the PcsScrapValue field I
want the LbsScrapValue field to use the formula to return the conversion to
pounds in that field. But, i also want to be able to type a number into the
LbsScrapValue field and have it return that value. (I have both the data
entry form and the report where this data needs to print pulling from a
query) I used the following argument in the expression builder in the report
but its not working. What did I miss?

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])
Click to expand...

Just setting the control source of a textbox on the form will display the
result of the calculation, but not store it in the table. But maybe that's a
good thing!

The proper normalized design would be to store *ONE OR THE OTHER* of
LbsScrapValue and PcsScrpValue, not both. The problem if you store both in the
table is that they could be stored inconsistantly! Are these dollar amounts?
Is it legitimate for the LbsScrapValue to be *different* than this expression?
If not, then you should decide to store just one of them, and routinely
calculate the other one when it's needed.

I have a vague picture of what you're working on, but perhaps a bit of
explanation of your project would help.
P.S. I sure do appreciate your patience and the help you are giving me.
Click to expand...

Like the other volunteers on the group, I just enjoy paying forward some of
the help I've gotten over the years.
Click to expand...
 
John,

What I have created is a database to control our returned goods
authorizations. The process starts with issuing an RGA to a customer for
material. That material can be quantified in terms of pounds or pieces,
depending on the customer. My tables simply store either the pieces or
pounds (in separate fields) being returned but my end report needs to state
total pounds regardless of how the customer reported it. Thus the
calculation from pieces to pounds. I abandond storing the converted data
based on your suggestion from your first post. What Im trying to do is in my
report, if there is no value in the LbsScrapValue field it converts the
PcsScrapValue field to pounds (and displays in the LbsScrapValue field. But
if there is a value in the LbsScrapValue field it displays that value.

When I use the formula below everything works fine if Im just converting
piece to pounds.
=(([Length] / 12) * [WeightPerFoot]) * [PcsScrapValue])

But what I want to add is a condition that says if there is a value in the
LbsScrapValue field to display that value but if there is NO value in that
field then to do the calculation from the PcsScrapValue field converting it
to pounds. I thought this was how to write that conditional expression but
its not returning correct values.

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])
Click to expand...

The OR operator doesn't do what you are assuming. It's a Boolean Algebra
operator, just as + or - are arithmatic operators. In Boolean Algebra, the
following definitions apply:

True AND True = True
True AND False = False
False AND True = False
False AND False = False
True OR True = True
True OR False = True
False OR True = True
False OR False = False

That is, AND returns true if and only if both its arguments are TRUE; OR
returns true if either argument is true.

What I think you want is

NZ([LbsScrapValue], (([Length] / 12) * [WeightPerFoot])* [PcsScrapValue])

This will return LbsScrapValue if there is something in that field, and will
return the value of the expression if there isn't. Note that if the field is
of Number datatype you do *NOT* want the quotemarks (Access will have to do an
extra step to convert from String to Number), and in any case, if the field is
NULL then it is not equal to either 0 or to "0".
 
I changed the expression to:

=NZ([LbsScrapVaue],(([Length]/12)*[WeightPerFoot])*[PcsScrapValue])

When I enter a number into the LbsScrapValue field everything works just
fine. When I enter a number into the PcsScrapValue field I get "#Error" in
the LbsScrapValue field. (All fields are number fields) The error says
"Invalid Control Property: Control Source, Circular Reference" What did I do
wrong?

Thanks,
~Rachele

John W. Vinson said:
John,

What I have created is a database to control our returned goods
authorizations. The process starts with issuing an RGA to a customer for
material. That material can be quantified in terms of pounds or pieces,
depending on the customer. My tables simply store either the pieces or
pounds (in separate fields) being returned but my end report needs to state
total pounds regardless of how the customer reported it. Thus the
calculation from pieces to pounds. I abandond storing the converted data
based on your suggestion from your first post. What Im trying to do is in my
report, if there is no value in the LbsScrapValue field it converts the
PcsScrapValue field to pounds (and displays in the LbsScrapValue field. But
if there is a value in the LbsScrapValue field it displays that value.

When I use the formula below everything works fine if Im just converting
piece to pounds.
=(([Length] / 12) * [WeightPerFoot]) * [PcsScrapValue])

But what I want to add is a condition that says if there is a value in the
LbsScrapValue field to display that value but if there is NO value in that
field then to do the calculation from the PcsScrapValue field converting it
to pounds. I thought this was how to write that conditional expression but
its not returning correct values.

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])
Click to expand...

The OR operator doesn't do what you are assuming. It's a Boolean Algebra
operator, just as + or - are arithmatic operators. In Boolean Algebra, the
following definitions apply:

True AND True = True
True AND False = False
False AND True = False
False AND False = False
True OR True = True
True OR False = True
False OR True = True
False OR False = False

That is, AND returns true if and only if both its arguments are TRUE; OR
returns true if either argument is true.

What I think you want is

NZ([LbsScrapValue], (([Length] / 12) * [WeightPerFoot])* [PcsScrapValue])

This will return LbsScrapValue if there is something in that field, and will
return the value of the expression if there isn't. Note that if the field is
of Number datatype you do *NOT* want the quotemarks (Access will have to do an
extra step to convert from String to Number), and in any case, if the field is
NULL then it is not equal to either 0 or to "0".
Click to expand...
 
I changed the expression to:

=NZ([LbsScrapVaue],(([Length]/12)*[WeightPerFoot])*[PcsScrapValue])

When I enter a number into the LbsScrapValue field everything works just
fine. When I enter a number into the PcsScrapValue field I get "#Error" in
the LbsScrapValue field. (All fields are number fields) The error says
"Invalid Control Property: Control Source, Circular Reference" What did I do
wrong?
Click to expand...

What's the Control Source of this textbox? It sounds like it's PcsScrapValue
or LbsScrapValue - you can't use either of these for *this control* because
the expression depends on those two fields. You can't define the value of a
field in terms of itself.
 
;vv;vhj

RacheleP said:
I changed the expression to:

=NZ([LbsScrapVaue],(([Length]/12)*[WeightPerFoot])*[PcsScrapValue])

When I enter a number into the LbsScrapValue field everything works just
fine. When I enter a number into the PcsScrapValue field I get "#Error"
in
the LbsScrapValue field. (All fields are number fields) The error says
"Invalid Control Property: Control Source, Circular Reference" What did I
do
wrong?

Thanks,
~Rachele

John W. Vinson said:
John,

What I have created is a database to control our returned goods
authorizations. The process starts with issuing an RGA to a customer
for
material. That material can be quantified in terms of pounds or pieces,
depending on the customer. My tables simply store either the pieces or
pounds (in separate fields) being returned but my end report needs to
state
total pounds regardless of how the customer reported it. Thus the
calculation from pieces to pounds. I abandond storing the converted
data
based on your suggestion from your first post. What Im trying to do is
in my
report, if there is no value in the LbsScrapValue field it converts the
PcsScrapValue field to pounds (and displays in the LbsScrapValue field.
But
if there is a value in the LbsScrapValue field it displays that value.

When I use the formula below everything works fine if Im just converting
piece to pounds.
=(([Length] / 12) * [WeightPerFoot]) * [PcsScrapValue])

But what I want to add is a condition that says if there is a value in
the
LbsScrapValue field to display that value but if there is NO value in
that
field then to do the calculation from the PcsScrapValue field converting
it
to pounds. I thought this was how to write that conditional expression
but
its not returning correct values.

= [LbsScrapValue] Or IIf ([LbsScrapValue] = "0", (([Length] / 12) *
[WeightPerFoot])* [PcsScrapValue])
Click to expand...

The OR operator doesn't do what you are assuming. It's a Boolean Algebra
operator, just as + or - are arithmatic operators. In Boolean Algebra,
the
following definitions apply:

True AND True = True
True AND False = False
False AND True = False
False AND False = False
True OR True = True
True OR False = True
False OR True = True
False OR False = False

That is, AND returns true if and only if both its arguments are TRUE; OR
returns true if either argument is true.

What I think you want is

NZ([LbsScrapValue], (([Length] / 12) * [WeightPerFoot])* [PcsScrapValue])

This will return LbsScrapValue if there is something in that field, and
will
return the value of the expression if there isn't. Note that if the field
is
of Number datatype you do *NOT* want the quotemarks (Access will have to
do an
extra step to convert from String to Number), and in any case, if the
field is
NULL then it is not equal to either 0 or to "0".
Click to expand...
Click to expand...
 
Back
Top