Trouble using expressions

[sachin123]

hi all,

ive got a table called seats and it containes the following fields:

Flgiht_No.
Seat_No.
Cost
Points_Worth
Cash
Points_Paid

i want to make the expression;
Points_Paid=Points_worth*(1-Cash/Cost)
such that as soon as u enter the fields cost, cast and points worth it updates the field Points paid.

access is giving me the error that:"The database engine does not recognise either the field Points_worth in validation expression, or the default value in the table 'seats'"

any thoughts would be appreciated. oh and warning: noob alert

 

[namliam]

You should not store calculated values in your Table(s), doing this is bad bad practice.

Calculations like this should be done in Queries where you can easily do just that what you are doing...

P.S. Welcome to the forum

 

[sachin123]

the reason i want to calculate values like this is so that i want to update the next table automatically without me actually having to update it.

for example theres a table called Accumulated points:

it has fields:
CustomerID
Points
Date
Time

so I want points (in AccPoints table)=PointsWorth-PointsPaid(in Seats table)

is that still bad practice? i dont see why this should be done in queries only because i would expect (as a beginner - that this to be a basic feature)

am i supposed to use a action query?

oh and thanks for the reply

 

[namliam]

No Action queries are still going to store data...

One of the basic rules of databases is "do not store calculated values...
Example:
Table1: (3 records)
1
1
2

Table2:
4 (total of table1)

Now I add a record to table1:
-1

Now I need to edit table 2 to display 3 instead of 4.
If however I have a (normal) select query:
Select Sum(Field) from table1
This will ways give me the right number, even if I add 2, 10 or 1000 or 1.000.000 records it will always be right.

Ofcourse above is a simple example but that is the basic of it. To to learn about Normalizing databases before you start using them... Will save a lot of headaches