calculated default value

[sarangdw]

Is it possible to calculate a default value and save it in
a table, I would like the default value in a table to be
the sum of two already entered fields. I could make a
query that calculates this however I am not able to save
this 'calculated field' in my original table

 

[Rick B]

You would almost never save a calculated result to a table. If you need the
particular number in a report, query, or form, you'd calculate it there.
Saving a calculated result is redundant and poor database design.

The only time this might be needed is if the values change and you need to
save a calculation from a particular point in time. For example, the price
you pay for a particular product varies each time you buy it and you need to
store the price you paid at a given point in time.

If you search through these newsgroups you will find that same answer posted
a hunded times.

Rick B

 

[Allen Browne]

You cannot set the Default Value property of a field in table design, such
that it defaults to the sum of 2 other fields. Access calculates the default
value before you begin adding the new record, so the value of the other 2
fields has not been entered at that time.

See:
Calculated fields
at:
http://members.iinet.net.au/~allenbrowne/casu-14.html

The article explains that you should not store the calculated value, unless
there is some reason why it should sometimes be different from the sum of
the other fields. If that is so, it explains how you can achieve that by
using a form to enter the data. (Note that you can make the form look like a
datasheet if you wish.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thanks for this, however I still have a problem to
resolve .

The reason why I would like this calculated record to be
saved is that it is a summary of data in other tables, it
would be unique data and I would like to index it. This
unique key would be the link to another table where it is
used as an primary key. ex. field1 '00005' field2 '10010'
field3 'aanxb' field4 '0101' would be
A00005B10010CaanxbD0101

Now I would be able to search for the whole string without
running a query in 4 different tables.

THX

 

[Brian Camire]

You can create a unique index on the combination of all four fields in your
table (see the "Create a multiple-field index" topic in the help). You
could then search that one table on these four fields and expect that your
index would be used.

 

[John Vinson]

Thanks for this, however I still have a problem to
resolve .

The reason why I would like this calculated record to be
saved is that it is a summary of data in other tables, it
would be unique data and I would like to index it. This
unique key would be the link to another table where it is
used as an primary key. ex. field1 '00005' field2 '10010'
field3 'aanxb' field4 '0101' would be
A00005B10010CaanxbD0101

Now I would be able to search for the whole string without
running a query in 4 different tables.

Note that you can set a Primary Key on up to TEN fields. It is neither
necessary - nor good practice! - to store the data redundantly in
another field just in order to create an index.

You can also link on multiple fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps