Normalization Problem with Calculated Value

[dgutsche]

Hi there



The more posts I do the more inept I feel ha ha. Anyway I have a desire to do something that violates normalization. I want to calculate a value called "Break Date" which is determined by adding two static fields "Cast Date" and "Age" together. This value would be store-able for a simple purpose. I want the user to be able to edit the "Break Date" for samples where it serves the application.

Everything I read says "DON'T DO IT" (since I can generate the break date with a query whenever I want it anyway). But I need to understand why. For outputting the final report I wouldn't call the value "Age" I would use the final "Break Date" and the initial "Cast Date" to report the actual age (calculated backwards using a difference). That way my users are free to edit the value of "Break Date" which includes situations where we are closed (Sunday) and the break just does not occur until Monday or Tuesday.

Why is everyone saying this is such a bad approach?

Thanks!

[June7]

That's why we do breaks on weekends and holidays. Acceptance samples can't be delayed.

Options:

1. include consideration of weekend days (and holidays?) in formula to calculate break date

2. rely on user to fix the date

[dgutsche]

Ahh interesting. In our case we rarely get samples that NEED to broken on a Sunday. Extending the date one day doesn't wreck the testing as they have several samples from which to extrapolate the curve. Shifting one of the points in the analysis doesn't detract too far from the quality of the output.

SO I think I will go with your second option. I think then my second form (a Break Submission form) will allow the user to overwrite the age (though they won't know it) by submitting the day the sample was broken.

Thanks again June7!

[ssanfu]

Everything I read says "DON'T DO IT" (since I can generate the break date with a query whenever I want it anyway). But I need to understand why.

OK, lets say you want to keep track of your projected gross income. You create a table that stores your hourly rate, the number of hours worked and the product - the total dollars.
So you are entering your hours, the hourly rate and the total dollars. So far, so good.

But then you have to go back 7 days and change the number of hours worked for 1 day. If you forget to also update the total dollars, you have an error - the total dollars for that day is wrong.

It is much better to have the total dollars calculated every time. The total dollars will never be wrong..... IF you entered the data accurately

BUT, there are times when it makes sense to store the calculation. I store calculated values when necessary. It is a business decision.

Since you want to be able to adjust the date at times, it makes sense to store the calculated date.

My $0.02.......

[June7]

I do save a LOT of calculated data in the laboratory db. Most of the calculations are too complicated to rerun every time a report is generated. And we don't want test results to be changed if in the future the formula is changed (happened once). So the final calculated results become our 'raw' data, which is what shows on the reports.

I should have suggested a third option.

Calculate the break date and have it consider weekends and holidays but allow user to change. This way if they forget, you still have a value that is probably correct.

Date calculation to account for weekends and holidays a fairly common topic. https://www.accessforums.net/reports...ate-27332.html