calculations

[BobC]

I am new at Access ... so please give many details ...
I want to use a form for a user to create and or modify some portions of
records of a table.
Either simultaneously with the user input or at the conclusion of the
modifications of a record, I would like to automatically do some
calculations, the results of which are to be placed in other fields of
the same record in the table.
The results will then become the basis of a report.
The calculations may involve some comparisons and simple math.
Is this a difficult task?
Bob

 

[John Vinson]

I am new at Access ... so please give many details ...
I want to use a form for a user to create and or modify some portions of
records of a table.
Either simultaneously with the user input or at the conclusion of the
modifications of a record, I would like to automatically do some
calculations, the results of which are to be placed in other fields of
the same record in the table.
The results will then become the basis of a report.
The calculations may involve some comparisons and simple math.
Is this a difficult task?

It's not hard. But it's a task that SHOULD NOT BE DONE.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[BobC]

I believe I understand what you are saying. In this case, the table is
not particularily big (about 800 records with about 20 fields). At some
point, I want the resulting data to be stored in order to have a record
of the calculated financial results. I do not intend to have the
calculated fields available for editing.
Does this make sense?
An underlying problem may be that I will probably need to use logical
(IF) functions to create the calculated results ... I am not even sure
if I can do that??
e.g. 'functionally' ... IF NumberOfUnits >=1000 THEN OrderQuantity =
NumberOfUnits/5

 

[Rick Brandt]

I believe I understand what you are saying. In this case, the table is
not particularily big (about 800 records with about 20 fields). At some
point, I want the resulting data to be stored in order to have a record
of the calculated financial results. I do not intend to have the
calculated fields available for editing.
Does this make sense?
An underlying problem may be that I will probably need to use logical
(IF) functions to create the calculated results ... I am not even sure
if I can do that??
e.g. 'functionally' ... IF NumberOfUnits >=1000 THEN OrderQuantity =
NumberOfUnits/5

You do the calculation in a query and use that instead of the table. There
is no reason for them to be in the table itself.

Think of an Excel sheet with numbers in the A and B columns and you want
the C column to display the sum of the A column plus the B column. Would
you create a macro to perform the calculation and place the results into
column C or would you use an expression in column C to perform the
calculation on-the-fly? Storing calculated results in tables would be
equivalent to the former.

 

[BobC]

Part of the problem is that I do need to store the results somewhere and
save the results ... for audit purposes.

 

[BobC]

As a furthur explanation ... using your example ...
On the 31st of Nov, I want to use whatever the contents of Column A and
Column B are at that time ... perform some math using these values (as
of 31st of Nov) and store the results in Column C ... after that my
reports will only use Column C and will tell me what the answer was on
31st of November ... even if A and B were to be changed.

 

[Rick Brandt]

Part of the problem is that I do need to store the results somewhere and
save the results ... for audit purposes.

But if your table already includes all of the values required to perform
the calculation then those are the values that need to be audited. The
results of the arithmetic aren't going to change.

I'll say it again. If you perform the calculations in a query, then that
query will look and act and can be used exactly the same as if it were a
table that had the results of the calculations stored in it.

 

[BobC]

But if your table already includes all of the values required to perform
the calculation then those are the values that need to be audited. The
results of the arithmetic aren't going to change.

 

[BobC]

Another problem is that I STILL need to store the results somewhere so I
can access them with other reports. The results represent a 'snapshot'
of what the result was at the end of the month. I do not believe I can
store it in a query. Also, I don't want the 'saved copy of the table'
accessed the following month.

Would you please just help me with what I want to do ... if it doesn't
accomplish what I want to do, it will be my problem.

 

[John Vinson]

I am new at Access ... so please give many details ...
I want to use a form for a user to create and or modify some portions of
records of a table.
Either simultaneously with the user input or at the conclusion of the
modifications of a record, I would like to automatically do some
calculations, the results of which are to be placed in other fields of
the same record in the table.
The results will then become the basis of a report.
The calculations may involve some comparisons and simple math.
Is this a difficult task?
Bob

Ok... based on the information downthread, it sounds like you do have
a legitimate need to store the result of the calculations at a point
in time.

Probably the easiest way to do this is to have a Form with two sets of
textboxes, one to do the calculations, the other bound to the table
fields. Set the Control Source properties of the first set to the
expressions that do the calculation, e.g.

=IIF([Category] = "Taxable", [Price] * [TaxRate], [Price])

or whatever expressions will do the job.

Then in the Form's BeforeUpdate event, set the value of each bound
control to the corresponding calculated control:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code goes here, and sets Cancel to True if
there's a problem with the data>
Me!txtTotalPrice = Me!txtCalcTotalPrice
Me!txtThis = Me!txtCalcThis
Me!txtThat = Me!txtCalcThat
End Sub

 

[JohnWL]

You'll need to use a form that is bound to the table, with textboxes for A,
B and C, each bound to its field in the table. When the data is entered or
modified for A and B, use code in the After Update event for each of those
(or use a "Calculate" button with this code in the On Click event) which
calculates the value for C and stores it in the textbox for C. That will
store it in the field C of the table as you wish. But you have to understand
that if the data in A or B is changed outside of this form, the value in C
will not be reliable.

Or you can create an update query that reads A and B, calculates C and
stores it in C for all records in the table. This can be run from a command
button on a form created just to calculate C in all records at the push of a
button. This may be preferable, depending on your situation.

And 'Yes' to your other question which no one answered. You can do complex
calculations on the data to derive the results you need. Its not difficult.
In VBA code, you can calculate based on a very complex formula using If ...
Then statements, etc.

You may wish to include a timestamp field, Now(), in the table also so you
know when this value was calculated, as an additional source for verifying
suspicious data later (also a normal component of good audit trails).

I hope you realize, based on what all you've revealed thus far, that if the
data in field C can be preserved by not using that table any longer after
the end of the month (saved copy of the table), then the values in fields A
and B are also preserved. Thus calculating the value of C in a query or
report at a later date from A and B is still just as valid as saving a copy
of the calculated results, and so its still not necessary to save C in a
table.

On the other hand, if you re-use the table from month to month to update A
and B, what will prevent C also being updated and thus losing the value as
of some earlier date? You implied that A and B could be modified after the
value of C has been stored.

I have a feeling that you might be better off creating a separate table to
store audit trail data - date, values of A and B as of that date, etc.
Again, C can be calculated from those at any time in the future. Saving
copies of the table each month seems clumsy, a hassle to be sure it gets
done every time, and prone to more errors.

Consider this. You create an all purpose query that reads A and B and
calculates C into a separate field in the query results. Then any form and
each report which need these values uses that query as its data source. You
gain the best of all worlds - proper table structures, reliable data, speedy
results.

I agree strongly that it is not good to store calculated results in a table.
But most professional programmers will admit to doing it at times for the
sake of expediency. As long as you understand the implications for
corrupting your data and take careful steps to avoid it, do what you must to
make the database do your bidding.

This is a bit long, but I hope I gave you the info you need and some ideas
to ponder.
John Loewen


As a furthur explanation ... using your example ...
On the 31st of Nov, I want to use whatever the contents of Column A and
Column B are at that time ... perform some math using these values (as of
31st of Nov) and store the results in Column C ... after that my reports
will only use Column C and will tell me what the answer was on 31st of
November ... even if A and B were to be changed.


Rick Brandt wrote:


I believe I understand what you are saying. In this case, the table is
not particularily big (about 800 records with about 20 fields). At some
point, I want the resulting data to be stored in order to have a record
of the calculated financial results. I do not intend to have the
calculated fields available for editing.
Does this make sense?
An underlying problem may be that I will probably need to use logical
(IF) functions to create the calculated results ... I am not even sure
if I can do that??
e.g. 'functionally' ... IF NumberOfUnits >=1000 THEN OrderQuantity =
NumberOfUnits/5


You do the calculation in a query and use that instead of the table. There
is no reason for them to be in the table itself.

Think of an Excel sheet with numbers in the A and B columns and you want
the C column to display the sum of the A column plus the B column. Would
you create a macro to perform the calculation and place the results into
column C or would you use an expression in column C to perform the
calculation on-the-fly? Storing calculated results in tables would be
equivalent to the former.

 

[BobC]

John,

THANK YOU so much for taking the time to answer my question(s) as well
as giving me some options as to other ways to consider (which I am
reviewing)!
I got the message loud and clear that "it is not generally good practice
to store calculated results in a table"... I was about to give up on
getting a straight answer!

I will be reusing the table from month to month and will likely use your
suggestion of time stamping.

I have written a couple of VB6 simple applications, so I am hoping I can
do the VBA code! It has been a while!

Regarding:
"Consider this. You create an all purpose query that reads A and B and
calculates C into a separate field in the query results. Then any form and
each report which need these values uses that query as its data source. You
gain the best of all worlds - proper table structures, reliable data, speedy
results."
I am not sure I know how to or what you mean by "place the results in a separate field in the query results"?

Let me give you a better idea (hopefully!) of what I want to do:
Have the User input time/date stamped data (changes) during the month ...each month.
At the end of the month, they will print several reports.
There are about 13 reports that use the results of calculated data.
I want the user to be able to continue to enter data (changes) for the following month (date-stamped).
The program will use the date-stamped entries to know which input data to be used for that month's calculations.
These reports (and data) need to be achieved.
The user needs to be able to continue entering data for next months reports, and be able to print 'preliminary' reports for the following month (Dec). At the end of the following month (e.g. Dec) they will again print preliminary and final reports and save the data.
It is hard say at this point as to how finding an error in last month's reports will be handled ???
(re-reading this ... I'm not sure how clear I made this)

At any rate, I very much appreciate the time and effort you have given to help me!

Bob

 

[BobC]

John,
Thanks!
I may have some follow-on questions ... I am obviously new at this.
Thanks Again,
Bob

I am new at Access ... so please give many details ...
I want to use a form for a user to create and or modify some portions of
records of a table.
Either simultaneously with the user input or at the conclusion of the
modifications of a record, I would like to automatically do some
calculations, the results of which are to be placed in other fields of
the same record in the table.
The results will then become the basis of a report.
The calculations may involve some comparisons and simple math.
Is this a difficult task?
Bob

Ok... based on the information downthread, it sounds like you do have
a legitimate need to store the result of the calculations at a point
in time.

Probably the easiest way to do this is to have a Form with two sets of
textboxes, one to do the calculations, the other bound to the table
fields. Set the Control Source properties of the first set to the
expressions that do the calculation, e.g.

=IIF([Category] = "Taxable", [Price] * [TaxRate], [Price])

or whatever expressions will do the job.

Then in the Form's BeforeUpdate event, set the value of each bound
control to the corresponding calculated control:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code goes here, and sets Cancel to True if
there's a problem with the data>
Me!txtTotalPrice = Me!txtCalcTotalPrice
Me!txtThis = Me!txtCalcThis
Me!txtThat = Me!txtCalcThat
End Sub

 

[Rick Brandt]

John,

THANK YOU so much for taking the time to answer my question(s) as well
as giving me some options as to other ways to consider (which I am
reviewing)!
I got the message loud and clear that "it is not generally good practice
to store calculated results in a table"... I was about to give up on
getting a straight answer!

I will be reusing the table from month to month and will likely use your
suggestion of time stamping.

I have written a couple of VB6 simple applications, so I am hoping I can
do the VBA code! It has been a while!

Regarding:
"Consider this. You create an all purpose query that reads A and B and
calculates C into a separate field in the query results. Then any form and
each report which need these values uses that query as its data source. You
gain the best of all worlds - proper table structures, reliable data, speedy
results."
I am not sure I know how to or what you mean by "place the results in a

separate field in the query results"?

EXAMPLE: Query with a single input table. The fields UnitPrice and
QtySold are pulled down from the table in the query grid. In the third
column of the query grid you make the following entry...

ExtendedPrice: [UnitPrice]*[QtySold]

Extended price will show the product from the expression and will always be
accurate regardless of what happens to the other two columns.

Let me give you a better idea (hopefully!) of what I want to do:
Have the User input time/date stamped data (changes) during the month ....each month.
At the end of the month, they will print several reports.
There are about 13 reports that use the results of calculated data.
I want the user to be able to continue to enter data (changes) for the

following month (date-stamped).

The program will use the date-stamped entries to know which input data to

be used for that month's calculations.

These reports (and data) need to be achieved.

Assuming you meant "archived". A report with a date filter will do all of
these calculations on the fly and you would get the same result running it
three years from now as you do today (assuming the base data hasn't
changed). The results do not need to be "stored".