Whether to Include a Calculated Field in a Table

[Larry in TN]

I'm new to Access but have been using Excel since 1990. I'm working on a "simple" project as a way to learn Access. Not sure how to decide if a calculated field should be included as a column in the table or just calculated every time it is needed.

In this case, I have a table where each record is the data from one paycheck stub. There are fields for each earnings category and a field for each deduction. My question is if I should include a calculated field for Net Pay in the table or should I just calculate Net Pay with an expression whenever I need it?

I'm mainly interested in the understand WHY it is better to do this one way or the other. Trying to learn the concepts.



Thank you,

Larry

[JoeM]

Your example is a perfect example of a case where you should never store the calculation.
General rule of thumb, never store anything that can be calculated on the fly.

In a nutshell, it violates the Rules of Normalization, in which it says that fields should not be dependent upon other fields.
A good example why is the following. Let's say that you did store the calculation. Now, after the fact, you discover that an error was made in the deductions and you need to change the deduction value. Now if you change that, but forget to re-calculate the net pay amount, you have a data discrepancy!

By not storing calculations, you help to maintain data integrity and the dynamic nature of the database. If it can be calculated on the fly, there is no need to store it anyhow (it also takes up less space, as it is one less field you are storing).

For more on Data Normalization, see here: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[Larry in TN]

Thanks, Joe! I appreciate the detailed explanation. That makes a lot of sense.

I thought that was what I was supposed to do but saw some other threads where people were including calculated fields in their tables which made me realise that I didn't understand what was best and why.

[June7]

A calculated field in table is not saving a result. It is a dynamic structure that should automatically recalculate when any of the components are changed. This is a new feature with Access 2010. Some say it's great, some hate it.

Saving the result of a calculation into table requires code - macro or VBA. This is what Joe is advising against, as do I. However, there is a case where using code to save a value into table is justified. As an example, product prices can change over time but don't want revised price in Products table to impact calculation of existing purchase records, so saving into Purchases record the price in effect at time of purchase is one way to prevent distortion of historical data. Alternatively, don't change the prices in Products table, create a new product record and set the old one as 'inactive' with a Yes/No field. Then save the ProductID into Purchases table.


Your db structure does not appear to be normalized. A field for each earning category and for each deduction is an indicator of this.

[JoeM]

A calculated field in table is not saving a result. It is a dynamic structure that should automatically recalculate when any of the components are changed. This is a new feature with Access 2010. Some say it's great, some hate it.

Yeah, I hate it! I see no need to do this, what it adds when you can already do that in a query.

And using that kind of structure is not compatible with more robust database programs like SQL.

[Larry in TN]

Hi June,

Thanks for the additional information. I'm still "thinking" in Excel and trying to learn how to "think" in Access. Some very big differences that will take some time to assimilate. I'm trying to learn to do it the right way so I don't end up with something that works but is a big mess.

Your db structure does not appear to be normalized. A field for each earning category and for each deduction is an indicator of this.

I don't quite understand this yet. I need to read more about normalization to understand exactly what that entails.

The db is just something I'm doing to give me a way to start learning Access. I really don't need the advanced features of Access to do it. I currently track this information in a single Excel table. It's just a list of my paychecks with a field for each item on the pay stub. i.e. Regular pay, bonus pay, incentive pay, per diem, etc. for earnings and all the various taxes, medical, 401k, etc. for deductions. I don't enter Net Pay, I calculate it. I'll also calculate the YTD for each category though I haven't built that Query yet.

I also project the rest of the year in my Excel spreadsheet and plan to do that in Access as well. In Access, I'm using a "Pending" checkbox to indicate if the record is the actual amount or a projection of a future check. I figure that I"ll be able to use that Yes/No field to Query either for YTD or YTD+Projection totals.

Eventually, I'll get to the point of automated the projection process with macros or VBA of some sort but that's quite a ways down the road from where I am now.

Does it still sound like the db isn't normalized? I'll try to read Joe's document on normalization tomorrow.

Thanks again...

[June7]

If this helps: what if you need to add another earning or deduction category? This would entail design changes to table, queries, forms, reports, code for the new field. Very annoying. Whereas if you have a field for the deduction amount and another field for the deduction type, modifying to add a new category could be as easy as adding a new record to Deductions lookup table.

I understand that it is unlikely in a payroll situation that new categories will be added, however, it is not impossible.

IMO, it is a balancing act between normalization and ease of data entry/output. You have to decide how far to take it.

[Larry in TN]

Yes, that makes sense!

I had thought about the problem of needing to add fields but it didn't occur to me to do it that way. I'm not sure exactly how it'll all come together on data entry forms, reports, etc. but I do understand the direction to go.

Thanks!

Larry

[June7]

And actually, earning and deduction categories can all be in one lookup table: FiscalCodes - with fields for Code, CodeType (earning or deduction), Description.

[Larry in TN]

Thanks, June. You've given me a lot to think about and try out. This is exactly what I started this project!