Calculations & Expressions - rule of thumb regarding the placement in tables, queries, reports, ...

[GlenR]

Greetings Access Forum,
I'm designing a db and have 11 tables. Many are just for reference but two (2) can begin calculations however, after reading many help files, I can place #calcs anywhere but, what is a #ruleofthumb? easy calcs in the tables, final calcs in reports ... any thoughts?

Thanks much in advance.
GlenR

[RayMilhon]

Greetings Access Forum,
I'm designing a db and have 11 tables. Many are just for reference but two (2) can begin calculations however, after reading many help files, I can place #calcs anywhere but, what is a #ruleofthumb? easy calcs in the tables, final calcs in reports ... any thoughts?

Thanks much in advance.
GlenR

Personally I would never put a calculation in a table. Anywhere else ok but not in a table.

[June7]

In spite of bad rap, I don't think calculated fields in table are terrible but they are limited. This really is a personal preference decision.

[Micron]

Calculation capability is limited. Generally speaking, one should not store any calculation unless it is necessary - and that is almost never.
Google it and see how many are on that side of the fence.

[NoellaG]

I don't think calculated fields in Access are persistent, so no harm done in terms of storing on disk. It has the advantage that the calculation is always performed, and you can create forms directly on the table without extra queries or extra calculated fields on the form.
If calculations are persistent, you have the advantage you can index them. So I think doing the calculations in the table can be quit helpful.

[CJ_London]

If calculations are persistent, you have the advantage you can index them. So I think doing the calculations in the table can be quit helpful.

No you can't - at least not in an access table - although you do imply that in your first sentence

no index property in the field properties and cannot select in the indexes list

I agree with June's comments although I very rarely use them - I did come across the odd random issue where sorting/filtering or using the calculated field in a query or form calculation generates an error.

[ArviLaanemets]

I never use calculated fields (where table field contain a formula instead of value) in databases. In case there is a need, I have a regular field instead, which is filled by afterupdate event of form/various forms. And I use such fields only to manage recordsets the form is based on, or for reports - but never for any calculations.

An example:
You have a form where various devices are registered. The devices form has subform where is registered, to which user the current device and at which time was assigned (users can be persons like your employees, nonperson users like departments, or system users like 'in storage', 'broken', 'in repair', 'scrapped' etc.). You have another form, where you can register, to which department any user and at which time is assigned (with DepartmentID = Null for users who left or which were disabled at certain date). Now in devices table, you can add fields, where ID's of current (the one with latest assignment date) user and department are calculated (e.g. by subform's update event), whenever a new user for device is registered, or something in any record of subform is changed. And in in form where assignments of users to departments are registered, you must have the code, which will update the DepartmentID in CurrentDepartment field of devises table too.

Now you can filter your devises form (using combos to set the filter conditions, or setting 'No Filter' condition) by current user, or by current department. The filtering is practically momentary. You also can have reports which return the list of devices currently used by certain user(s), or in certain departments, simply by filtering some general report, which when not filtered, returns all devices.

[NoellaG]

a bit of a confusion in

No you can't - at least not in an access table - although you do imply that in your first sentence

In my first sentence I stated

I don't think calculated fields in Access are persistent

so not persistent means: not saved to disk and as a result no index possible.

And yes, I use calculated fields in tables. They have a performance penalty, but so have calculated fields in queries or views. Of course it is even better if the calculations can be done in memory in the front end without pulling the data from the database.