calculated data from forms to a table.

[orsonros]

Hi!

I want to know how to store calculated data from FORMS to a TABLE. Show
me or please explain the method.

Thanks

ors

 

[Ken Snell \(MVP\)]

This normally is not good database practice. Unless the data are based on
values that change over time and you need to store what the value was at
that immediate moment, you likely can use a query to recalculate the value
whenever you need it.

Otherwise, you can put a textbox on your form that is bound to the desired
field in the table, and use the form's BeforeUpdate event to calculate the
value and write the value into that textbox.

 

[John Vinson]

Hi!

I want to know how to store calculated data from FORMS to a TABLE. Show
me or please explain the method.

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.

John W. Vinson[MVP]

 

[John Vinson]

Ok, I understand the design reasoning here; but I have a related question.

I need to have Access create a quarterly financial analysis of sales which
involves pro-rating a fixed overhead cost over a dynamic count of items. A
single query or report will change each time the query is run. I'm thinking
that if I save each report based on the query as a different report this will
accomplish my needs.

Would like confirmation. If there's a better way, I'm all ears.

There is certainly a better way.

A Report is NOT a static object. It's just a dynanmic window, a way of
moving data from relational tables to paper, based on the current
value of the data. Once you've printed the paper, *that* is a static
object which can be put into a file cabinet (or, you can print to a
file and save that file in a folder).

Simply base your (single) Report on a (single) parameter query which
retrieves the data that you want reported. You don't say how the query
or the report will change, so I'm not sure I fully understand your
question!


John W. Vinson[MVP]

 

[DrZilboorg via AccessMonster.com]

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.

John W. Vinson[MVP]

Ok, I understand the design reasoning here; but I have a related question.

I need to have Access create a quarterly financial analysis of sales which
involves pro-rating a fixed overhead cost over a dynamic count of items. A
single query or report will change each time the query is run. I'm thinking
that if I save each report based on the query as a different report this will
accomplish my needs.

Would like confirmation. If there's a better way, I'm all ears.

TIA, David

 

[DrZilboorg via AccessMonster.com]

Ok, I understand the design reasoning here; but I have a related question.

[quoted text clipped - 5 lines]

Would like confirmation. If there's a better way, I'm all ears.

There is certainly a better way.

A Report is NOT a static object. It's just a dynanmic window, a way of
moving data from relational tables to paper, based on the current
value of the data. Once you've printed the paper, *that* is a static
object which can be put into a file cabinet (or, you can print to a
file and save that file in a folder).

Simply base your (single) Report on a (single) parameter query which
retrieves the data that you want reported. You don't say how the query
or the report will change, so I'm not sure I fully understand your
question!

John W. Vinson[MVP]

So much for the paperless office!!!

The "Dynamic Count" is the parameter which changes: Cost X / Count will
resolve to a different number each time the calculation is made. (Of course,
this denies the existance of inflation. Costs will change, as well, but not
nearly as often)

Thanks for your help.

 

[ARDS]

One way I getting rid of the paper would be to e-mail the report to yourself
then save the sent report in a folder. This is what I do with my daily
reports, but I'm extremely ignorant when it comes to Access and know of no
other way to save the reports, lol.

[quoted text clipped - 16 lines]

John W. Vinson[MVP]

So much for the paperless office!!!

The "Dynamic Count" is the parameter which changes: Cost X / Count will
resolve to a different number each time the calculation is made. (Of course,
this denies the existance of inflation. Costs will change, as well, but not
nearly as often)

Thanks for your help.

 

[Jerry Whittle]

I prefer to save such information in a PDF file so that it's very difficult
for someone to change. PDF995 makes a cheap shareware program.

The next version of Office is suppose to create PDFs without any other
software needed.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

One way I getting rid of the paper would be to e-mail the report to yourself
then save the sent report in a folder. This is what I do with my daily
reports, but I'm extremely ignorant when it comes to Access and know of no
other way to save the reports, lol.

Ok, I understand the design reasoning here; but I have a related question.

[quoted text clipped - 16 lines]

John W. Vinson[MVP]

So much for the paperless office!!!

The "Dynamic Count" is the parameter which changes: Cost X / Count will
resolve to a different number each time the calculation is made. (Of course,
this denies the existance of inflation. Costs will change, as well, but not
nearly as often)

Thanks for your help.

 

[John Vinson]

So much for the paperless office!!!

The "Dynamic Count" is the parameter which changes: Cost X / Count will
resolve to a different number each time the calculation is made. (Of course,
this denies the existance of inflation. Costs will change, as well, but not
nearly as often)

I think you need to make a distinction between a Report and the data
upon which the report is based.

If you need to be able to store a history of the cost and count as of
every point in time that they change, and generate a report for the
Dynamic Count as of any chosen time, you can keep the data in a
historical table with a date/time field. You can then create a query
to select the data as of that point in time, and base a Report on that
query.

John W. Vinson[MVP]