Computed field

[micang]

Access 2003

Hi All,

Is it possible in a table to have a field value be computed from other fiels in the table?

Example: Field1,Field,Field3
A B AB

So field3 is the computed field by adding field1 and field2 together. So if Y is entered into field1 and Z into field2, then field3 will automatically disply YZ.

I would like to do this at table level because currently we have to input the data directly into the table.

Any info appreciated.

Michael

 

[straybullet]

This sort of thing cannot be calculated at the table level and is not normally stored in the table. It would be calculated as needed for display in reports/forms through use of a calculated field in the underlying query.


Let them hate - so long as they fear... Lucius Accius

 

[DarthWHO]

While you can't do the calculation directly in the table, there are a few things you can do:

A) If the data is manipulated through a form, you can change the field automatically whenever the record is updated or created

B) Run an Append Query to do all the work for you instead of manually.

"Expr1: [YourTable]![FieldA]+[YourTable]![FieldB]" and Append this to FieldC for example

Using Method B, you can also avoid updating all the records by choosing a set of criteria in your query.

 

[micang]

Thanks so much for the input and advice guys, really appreciated.

Michael

 

[lespaul]

another option, don't store the concatenated value in a field at all, anytime you need it run a query:

SELECT Field1, Field2, Field1 & Field2 As Field3 FROM TableName

storing calculated values breaks normalization rules, read the fundamentals document linked below for more on the rules of database design.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[micang]

Thank you Leslie, much appreciated.

Michael

 

[missinglinq]

Of course, the point no one's made as yet, is that you should never enter data directly into a table! It only takes about thirty seconds to use the Forms Wizard to run up a data entry form for a table! Then any type of data manipulation, including your problem posted here, can be easily accomplished!

Good Luck!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[micang]

missinglinq, you are dead right, and I have do start doing that.

Thanks

Michael

 

[micang]

Hi All,

I have created a form for the table and put in the formula to populate my field from the other 2 fields. It is working fine on the form, but it is not updating the table with the computed value.

I assume I must tell it to do this, but for th elife of me I can't find where.

Any assistance appreciated.

Michael

 

[lespaul]

again, why would you want to update the table with that information? In your table you have two fields Field1 and Field2. The source of the form should be a query that contains the calculated value:

SELECT Field1, Field2, Field1+Field2 As Field3 From TableName

Anytime you need to see the concatenated value, run a query and put the two fields together into one. You DON'T have to store that value in the table, in fact storing the calculated values BREAKS THE RULES!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[micang]

Leslie, thank you, the penny has finally dropped! We receive a file that I import into a table that has this computed field. But now I see that infact thisis a useless field that I not even have import, as it's just a waste of space etc.

Many thanks for your persistance

Much appreciated.

Michael