updating a lot of calc values that were not stored in the tables

[sparks]

I am working on a database that has a lot of calculated values on the
forms. These were never put into the tables. But were tied to unbound
fields on the forms.
Now 8000 records later they want these values in the tables.
All of this is done in modules after update of certain values entered
on the forms.
The calculations are called on current of the forms so they are
recalculated each time the form is opened.

I have no idea why it was not stored in the tables and the creators
are long gone.

can I do a query that updates fields and call functions in a module to
update with?

Its kinda convoluted since some of these are based on other table
values and then this produces some values that are used to calculate
some of the other values.

To calc B -- you calc A (this will be stored now) based on values in a
lookup table then use this to calc B(new stored value)

thanks for any ideas. I can scroll thru all records and have to forms
update each record ....NOT a good idea with 8000 records LOL

[Rich P]

It sounds like the values you are trying to retrieve are/were summary
type values - count of Totals for some parameter(s) perhaps? And thus
not stored.

The point of storing summary type values would be if the snapshot of
that data were no longer available. Example: you have an active data
table and a history data table. You want to summary the Active table at
some point in time and store these summary values because the active
table will change and that particular snapshot will no longer be
available.

But if the values were a count of items for a given data period - those
items would still be in your system and you could run a query to
re-summarize those values as many times as you want. Thus, no need to
store these values assuming the data for those parameters has not
changed. This would be like a static table.

If your table(s) is/are static - just create some queries to
re-summarize your values. If you need help with that -then post some
samples of the data you need to calculate.

If you table(s) is/are dynamic - like my active table example - where
you need to store a snapshot of the summary of the active table at some
point in time - this will be more difficult. But it sounds like your
tables are the static type that only collect data.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[sparks]

No its static values.
All I can say is the boss came to me and said where is this data?
I looked at the database and said they are being calculated on each
record for display only.
He asked why?
I laughed and said how the F do I know...ok I said that to myself
LOL
I checked and the people who did it are long gone.
This should have been put in the tables I have no way of knowing why
it wasn't done. I bet there was a reason but ???

On 03 Oct 2008 20:08:08 GMT, Rich P <rp*****@aol.comwrote:

>It sounds like the values you are trying to retrieve are/were summary
type values - count of Totals for some parameter(s) perhaps? And thus
not stored.

The point of storing summary type values would be if the snapshot of
that data were no longer available. Example: you have an active data
table and a history data table. You want to summary the Active table at
some point in time and store these summary values because the active
table will change and that particular snapshot will no longer be
available.

But if the values were a count of items for a given data period - those
items would still be in your system and you could run a query to
re-summarize those values as many times as you want. Thus, no need to
store these values assuming the data for those parameters has not
changed. This would be like a static table.

If your table(s) is/are static - just create some queries to
re-summarize your values. If you need help with that -then post some
samples of the data you need to calculate.

If you table(s) is/are dynamic - like my active table example - where
you need to store a snapshot of the summary of the active table at some
point in time - this will be more difficult. But it sounds like your
tables are the static type that only collect data.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Chuck]

On Mon, 06 Oct 2008 11:33:11 GMT, sparks <sp****@comcast.netwrote:

>No its static values.
All I can say is the boss came to me and said where is this data?
I looked at the database and said they are being calculated on each
record for display only.
He asked why?
I laughed and said how the F do I know...ok I said that to myself
LOL
I checked and the people who did it are long gone.
This should have been put in the tables I have no way of knowing why
it wasn't done. I bet there was a reason but ???

The reason is "normalization". Don't clutter up the database with values that
can be calculated.

Does your boss want to change any of the calculated data?
If yes. explain to your boss why it is calculated.
If not then:
Teach your boss to look at reports, not tables.
Or
Generate Excel files and let you boss look at those.
Or
Make new fields in the tables to hold the calculated data. Run the query where
the calculations are being made. Change the query to an update query. Update
all the calculated data to the new fields in the table(s) that the query is
based on. Change query back to Select Query.
Not good practice, but you can live with it if it makes your boss happy.

Chuck
--

>

On 03 Oct 2008 20:08:08 GMT, Rich P <rp*****@aol.comwrote:

>>It sounds like the values you are trying to retrieve are/were summary
type values - count of Totals for some parameter(s) perhaps? And thus
not stored.

The point of storing summary type values would be if the snapshot of
that data were no longer available. Example: you have an active data
table and a history data table. You want to summary the Active table at
some point in time and store these summary values because the active
table will change and that particular snapshot will no longer be
available.

But if the values were a count of items for a given data period - those
items would still be in your system and you could run a query to
re-summarize those values as many times as you want. Thus, no need to
store these values assuming the data for those parameters has not
changed. This would be like a static table.

If your table(s) is/are static - just create some queries to
re-summarize your values. If you need help with that -then post some
samples of the data you need to calculate.

If you table(s) is/are dynamic - like my active table example - where
you need to store a snapshot of the summary of the active table at some
point in time - this will be more difficult. But it sounds like your
tables are the static type that only collect data.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[sparks]

Well since one value is based on the previous value(s) I had to make
10 update queries to put the calculated values in the table.
It worked and they have all the values in the tables.
on the forms I tied the unbound fields to these value so any new data
or changes to existing values are put into the tables too.
one question some of these update queries are tied to functions that
open lookup tables so they take some time to run. (6 to 15 seconds) on
the 8000 records.
Just for my future info is there a way to call each query in a
function 1 to 10 so that it runs 1, finishes, then runs 2....etc
On Mon, 06 Oct 2008 10:09:47 -0400, Chuck <li*****@schoollink.net>
wrote:

>On Mon, 06 Oct 2008 11:33:11 GMT, sparks <sp****@comcast.netwrote:

>>No its static values.
All I can say is the boss came to me and said where is this data?
I looked at the database and said they are being calculated on each
record for display only.
He asked why?
I laughed and said how the F do I know...ok I said that to myself
LOL
I checked and the people who did it are long gone.
This should have been put in the tables I have no way of knowing why
it wasn't done. I bet there was a reason but ???

The reason is "normalization". Don't clutter up the database with values that
can be calculated.

Does your boss want to change any of the calculated data?
If yes. explain to your boss why it is calculated.
If not then:
Teach your boss to look at reports, not tables.
Or
Generate Excel files and let you boss look at those.
Or
Make new fields in the tables to hold the calculated data. Run the query where
the calculations are being made. Change the query to an update query. Update
all the calculated data to the new fields in the table(s) that the query is
based on. Change query back to Select Query.
Not good practice, but you can live with it if it makes your boss happy.

Chuck

[Rick Brandt]

sparks wrote:

No its static values.
All I can say is the boss came to me and said where is this data?
I looked at the database and said they are being calculated on each
record for display only.
He asked why?
I laughed and said how the F do I know...ok I said that to myself
LOL
I checked and the people who did it are long gone.
This should have been put in the tables I have no way of knowing why
it wasn't done. I bet there was a reason but ???

Because they should NOT have been. Whoever set it up did it correctly.
Your boss is wrong.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Chuck]

On Mon, 06 Oct 2008 18:26:54 GMT, sparks <sp****@comcast.netwrote:

>Well since one value is based on the previous value(s) I had to make
10 update queries to put the calculated values in the table.
It worked and they have all the values in the tables.
on the forms I tied the unbound fields to these value so any new data
or changes to existing values are put into the tables too.
one question some of these update queries are tied to functions that
open lookup tables so they take some time to run. (6 to 15 seconds) on
the 8000 records.
Just for my future info is there a way to call each query in a
function 1 to 10 so that it runs 1, finishes, then runs 2....etc

You can make a macro that calls the queries one after another.

Chuck

[Larry Linson]

"Rick Brandt" <ri*********@hotmail.comwrote

Because they should NOT have been. Whoever set
it up did it correctly. Your boss is wrong.

And, to Rick's comment, I'll add... unless the application is trashed,
sooner or later, you or someone else will be back with a problem -- "the
stored value does not match what would be calculated; how do I fix it?" And
the answer will be "don't store values that can be calculated when they are
needed". And the comment will be: "because sooner or later, they will rise
up like an alligator from the swamp through which you are slogging and bite
you in the tender places".

But, if your boss is willing to pay to foul up the design, and then will be
willing to pay to fix the problems he brought about, who are we to say what
he should do?

Larry Linson
Microsoft Office Access MVP