Expressions in a table?

[Jennifer]

Hello,
Love to use Excel, just learning the wonderful
capabilities of Access.

I understand that I can write expressions in a form or
query but how about in table. I have an expression that
gives me the total of pounds * price/pound in the form but
it doesn't seem to show up in the table when I go and
double check my entries. Very basic I'm sure and I know
you are saying this is better suited for Excel but I want
to better understand how to use this program. Writing
expressions etc. Thank you for any help you can give.
Jennifer

 

[fredg]

Hello,
Love to use Excel, just learning the wonderful
capabilities of Access.

I understand that I can write expressions in a form or
query but how about in table. I have an expression that
gives me the total of pounds * price/pound in the form but
it doesn't seem to show up in the table when I go and
double check my entries. Very basic I'm sure and I know
you are saying this is better suited for Excel but I want
to better understand how to use this program. Writing
expressions etc. Thank you for any help you can give.
Jennifer

Access works better when you stop thinking it's Excel. <g>

In Access, tables store the data needed for display in queries, forms,
and reports. A table need not ever be viewed, indeed most developers
often hide the tables from the end users.

There is normally no need to store the result of a calculation in your
table. Whenever you do need the calculated result, re-calculate it, in
a form, in a query, or in a report.

In your table, save the least amount of data needed to compute the
result. In this case, all you need is the Quantity and the Price.

 

[Alan Fisher]

You should not store calculated values in a table. Use a
query against the table and create a field to do the
calculation for you, then you can select that field when
building a form or report. To do this, put the cursor in
the Field line of a new feild and type TotalPrice:=
[Pounds] * [PricePerPound] ( assuming Pound and
PricePerPound are the names of the feilds you want
calculated). This gives you a field named TotalPrice with
the value you want. As you stated this can also be done on
a form or report by making the control source of a text
box equal to the same calculation. Then you would type the
following " =[Pounds] * [PricePerPound]" (without the
quotes) and you would get the same results. Hope this
helps.





--Original Message-----

 

[lbrinkman]

I disagree with some of what what said in the other two responses about NOT
storing
calculated values in a table.

If the UnitPrice in the query in the expression "TotalPrice:[UnitPrice] *
[Quantity]" never changed, then there would be no need to store the
"TotalPrice" in a table.

However, in the real world prices do in fact change with time. Thus, if the
UnitPrice
for Item X is $10.00 and the quantity purchased was 10, then the TotalPrice
would be $100. So, Mr. Jones owes you $100.

But let's say that next week you raise the UnitPrice of Item X to $12.00. If
you do NOT
save his TotalPrice owed to you in a table (but instead rely on a query to
recalculate using the TotalPrice: [UnitPrice] * [Quantity]), then when you
send him an invoice, the
TotalPrice on the invoice would be $120 -- not the $100 he actually owes.

Thus, in such a situation it is very important to save in a table the
calculated TotalPrice for that order. (I would save the TotalPrice in the
"Orders" table and the details about
each item purchsed in the OrderDetails table.)
---Phil Szlyk

 

[Chris]

I whole heartedly agree with you that this is one of the
few times that saving the results from calculations is a
good idea.

I think, in general, the above poster gave solid advice.
Jennifer, Excel and Access are two different worlds.


Chris

-----Original Message-----
I disagree with some of what what said in the other two responses about NOT
storing
calculated values in a table.

If the UnitPrice in the query in the

expression "TotalPrice:[UnitPrice] *

[Quantity]" never changed, then there would be no need to store the
"TotalPrice" in a table.

However, in the real world prices do in fact change with time. Thus, if the
UnitPrice
for Item X is $10.00 and the quantity purchased was 10, then the TotalPrice
would be $100. So, Mr. Jones owes you $100.

But let's say that next week you raise the UnitPrice of Item X to $12.00. If
you do NOT
save his TotalPrice owed to you in a table (but instead rely on a query to
recalculate using the TotalPrice: [UnitPrice] * [Quantity]), then when you
send him an invoice, the
TotalPrice on the invoice would be $120 -- not the $100 he actually owes.

Thus, in such a situation it is very important to save in a table the
calculated TotalPrice for that order. (I would save the TotalPrice in the
"Orders" table and the details about
each item purchsed in the OrderDetails table.)
---Phil Szlyk

Hello,
Love to use Excel, just learning the wonderful
capabilities of Access.

I understand that I can write expressions in a form or
query but how about in table. I have an expression that
gives me the total of pounds * price/pound in the form but
it doesn't seem to show up in the table when I go and
double check my entries. Very basic I'm sure and I know
you are saying this is better suited for Excel but I want
to better understand how to use this program. Writing
expressions etc. Thank you for any help you can give.
Jennifer

.

 

[Rick Brandt]

I disagree with some of what what said in the other two responses about NOT
storing
calculated values in a table.

If the UnitPrice in the query in the expression "TotalPrice:[UnitPrice] *
[Quantity]" never changed, then there would be no need to store the
"TotalPrice" in a table.

However, in the real world prices do in fact change with time. Thus, if the
UnitPrice
for Item X is $10.00 and the quantity purchased was 10, then the TotalPrice
would be $100. So, Mr. Jones owes you $100.

But let's say that next week you raise the UnitPrice of Item X to $12.00. If
you do NOT

You would not store the calculation result only if you ARE storing all of the
operands. In your example I would store both the qty and the price so if the
base price in the lookup table changes it does not affect old records.