Expressions in a table?

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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-----
 
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
 
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
Click to expand...
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
Click to expand...


.
Click to expand...
 
lbrinkman said:
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
Click to expand...


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.
 
Back
Top