Should I use Calculated Fields in Access 2010? (1 Viewer)

[MSAccess_newbie]

Hello All,

My question is, should I be using calculated fields in my database tables in Access 2010 or will it come back and bite me somewhere down the road?

My assumptions are:
I don't need my database to be backward compatible so I don't care if it wont run in Access 2007 or earlier.
I likely will not use complex calculations or one's not supported by the built in expressions.

I've read some posts which say that you should avoid calculated fields in tables and everything that you do in calculated fields can be done by running queries on the tables or do the calculations in the forms
I understand that, but for me it seems like just more work & maintenance to do that, it's just easier to type in the expression in the table.

Appreciate your views on this.....

Sherif

 

[John Big Booty]

Perhaps this article will help you come to a decision.

 

[MSAccess_newbie]

Hello John,

Thanks for sharing...

According to this artice, This seems to be the only concern for my case (as I don't intend to use earlier Access versions)
"If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results."

I guess its a trade of between ease of design and data integrity when changing the expressions. I wonder how often the 'may not be updated correctly' is and if anyone had a similar experience.

 

[John Big Booty]

Do you have some "special circumstance" that would prevent you from simply doing the calculation on the fly as you require it for display purposes

 

[The_Doc_Man]

There are situations for convenience, I suppose, but in something like 30+ years of experience with database systems (starting Digital Equipment's DATATrieve, going through Borland's DOS version of Paradox, and switching to Ac97 in about 1998), I have NEVER EVER found a case where using stored computations was truly necessary. Convenient, yes. Necessary, no.

The reason is that a query can be used as a data source in place of a table, but can contain your computation as one of its fields. Therefore, build your table, build a query on top of it and include computations based on the appropriate fields. Then build your forms or reports on the query, not the table. Problem solved.

 

[MSAccess_newbie]

Thank You Doc Man & Big Foot,

I wasn't doing it like that, I was building the query from the tables directly within each form I create, so it was kind of tedious.
But I see it makes sense as you suggest to make a base query first and then build the form on it. (As long as the query is editable and allows me to input data into the table - this solution will be fine.)

The other concern I had with the calculations done on the form directly, is controlling the updates... whenever a user makes inputs or changes to the source fields the calculated fields should display the results instantly. However I found that I have to sometimes requery the whole form so that the calculations are updated. Thinking about it again though, It may be that I'm not using the update events correctly...I think this deserves another try.

 

[599CD.com]

The ONLY instance I have come across where it's helpful to use calculated fields in tables is when you have a LOT of data and your reports are taking a LONG time to generate because of complex calculations.

I had this one client who had reports that were taking 30 to 60 seconds to generate - and it was a pretty fast computer. The problem was complex calculations. I switched her over to calculated fields, and the reports opened much faster.

In her case, which is rare, the extra little bit of storage space for an extra field, and the tiny bit of lost time on the data ENTRY side more than made up for the convenience of having her reports open up quicker. But, again, this is a RARE case. It's almost always better to use a query to perform the calculations.

Richard Rost
AccessLearningZone.com

 

[Pat Hartman]

However I found that I have to sometimes requery the whole form so that the calculations are updated.

You won't have this problem if you do the calculation in the query. When you do the calculation in a form, you either have to do it multiple places or put the calculation in the Form's BeforeUpdate event. So Units * Price needs to be recalculated in the AfterUpdate event for units and also in the AfterUpdate event for Price. If the calculation is in the query, Access figures this out and handles it for you.