How to store to a 'calculated' column of a MS Access table

[readams]

I have an MS Access 2000 table which contains the following columns/fields: (id, grade1, grade2, grade3, grade4, ages). Grade1 through Grade4 are of type Boolean, and ages is a String type. For a given row, the value of ages is a function of the values of grade1 through grade4, i.e., the value that is to be stored in ages is derived/calculated from the values in fields grade1 through grade4. Examples:

For a given row with the values of grade1 through grade4 as True, False, True, False, the field, ages, in that row should contain the string "grades 1, 3".

For a given row with the values of grade1 through grade4 as False, True, True, True, the field, ages, in that row should contain the string "grades 2-4".

I have written a Visual Basic user-defined function which correctly produces the resultant string based on the values of the grade1 through grade4 fields, but I do not know how to make the result be stored in the ages field of the table. Can anyone please help me?

(Right now, for testing purposes, I am outputting the string to a 'calculated control' on a form. The name of my function is "F1", and in the properties Control Source of the field on the form I have "=F1()". But I would like the result of my function to be stored in the ages column of the current row. How does one do that? Or am I going about this in completely the wrong way? Any help would be greatly appreciated.)

Thank you.

Richard E. Adams
Email: RAdams@dhs.ca.gov

 

[VicM]

Richard:

Is it necessary to store the calculated field in the table when it can be produced on a report or form by using your function on the report or form?

From a purist point of view, one should not store calculated data in a table containing the raw data from which it can be produced. It unnecessarily uses up storage. (Sorry, I get carried away sometimes.)

Vic

 

[readams]

VicM,

Thank you for replying to my question. I agree with you that one should not store calculated data in a table containing the raw data from which it can be produced. However, the person I am working for wants this to be done for reasons I am not clear about - possibly so that the calculated value only needs to be computed once, when the grade1 through grade4 values are entered; the resultant value would be stored in the ages field of the table at that time.

Regardless of the reason, doing this does not seem conceptually difficult. But being new to working with MS Access and Visual Basic, I am finding no documentation that explains how one would accomplish this. Can one store a value to the ages field directly from a Visual Basic function, and if so, how?

The function should be executed/evaluated whenever someone is entering data to the table via the table's corresponding data entry form, upon their selecting a combination of the grade1 through grade4 check boxes, or when changing the grade1 through grade4 check boxes, i.e., modifying/updating the values in those fields of an existing record. Could you also possibly explain how to 'program' MS Access so that the function is triggered at the appropriate time(s)?

Thank you.

Richard E. Adams
Email: RAdams@dhs.ca.gov

 

[uscitizen]

I would like to chime in with a correlated question wrt computed fields and tables. Eventually we will be exporting the data from the underlying MS Access table into another format which will be read by specialized statistical software. To enable the analysts to march ahead w/ little advance data management programming it'd probably be good to have the computed value for each record in the table itself. If that has been resolved I'd be grateful for the chance to review it.

Somewhat less related to the thread, I'd be grateful for a leg-up on how to populate a report with a computed value. Say I collect field_1 and field_2 in a form /table and have a report based on the their values for each record in the table. How then do I compute (to keep it simple) their product, i.e. 'field_1 * field_2' and have this value (let's call it 'field_3') appear on the report for each record in the table. I've tried several well intended approaches based on the documentation w/o much success.

 

[AlanSQL]

If you want to see the stored fields and the computed fields in the same "table", you can always build a query based on the table that includes all the table's fields but also the computed fields. It will look like a table, it will allow you to edit the fields in the base table, and it can be used as the basis of a form or report.

In fact, sometimes queries are used as the basis of forms and reports for security reasons, so the DBA can restrict access to certain users. A travel office clerk will not be given access to the Employee table because that table shows confidential info, like salery. Instead, a query is created based on the Employee table that excludes the confidential fields.

 

[flyboytim]

The reason why calculated values should be calculated freshly every time that view of the data is required, rather than stored with the raw data in a table, is as follows:
The raw data may change, for instance if an error in data input is found. In a small table it is trivial to recalculate the dependent calculated data for a few values, but what if the table is large, and has many records that need recalculating? Storing the calculated data will lead to uncheckable errors, unless you recalculate every record, so why store it at all?

If the query that calculates the new value is run, the resultant recordset may be exported to an external file for analysis, for instance a spreadsheet or an ODBC database

 

[hughesai]

I agree that calculated values should not be stored in a table. This leads to redundancy in the db, and can lead to errors if underlying data is changed, but the previously calculated field is not.

However, somtimes it is necessary to do so anyway. For example - I have a number of reports and charts which are reviewed weekly by management in my work. The queries to generate these charts take about 5 minute to run on a normal spec desktop PC. If I had the charts set up like this, the management would never review them - they are busy people - they need their information accurate and yesterday - 5 minutes from now is too late.

Instead I have a routine which runs all the necessary queries and updates a number of temporary tables every week. The charts are now based on the temorary table. They are now instantaneous, and the management are happy.

I do this using two methods.

Firstly, I have converted most of the relevant queries to make table queries. Calculated data is then stored in the newly made table. The raw data is untouched and remain in the main tables. The newly made tables are refreshed weekly and are published.

Secondly, some of the calculations anre not directly possible using a query, or are inefficient. In this case, I have my dummy table. I run a VB routine to open the table, delete all records, and then call an append query to add basic data to the table leaving some fields blank for later population. I then open the table as a recordset, loop through all the records, perform the necessary calculations, and write the results to the appropriate fields. Sample code is shown below:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim stSQL as String
Dim x
Dim intResult

set db=CurrentDb
stSQL = "SELECT tblTemp.* FROM tblTemp;"
set rst=db.Openrecordset(stSQL)

rst.movelast
rst.movefirst

for x = 1 to rst.recordcount

' Perform calculation eg, add field1 and field2, put
' result in field3

intResult = rst!field1 + rst!field2
rst!field3 = intResult

next x

set rst = Nothing
set db = Nothing


Hope this works - I just typed from memory - not always a good idea, especially with my typing.

Anyway hope it helps.

Aidan.

 

[flyboytim]

I totally agree that functionality sometimes means storing redundant values in a temporary results table and that queries cannot always provide all the data that is required. That is the principle behind OLAP and data warehousing, and many of the operations that are done on desktop database systems often lead to exporting recordsets to Excel spreadsheets and other tools for analysis. These often need not be real-time data, but a snapshot taken in order to produce a timely report.

uscitizen - you first need to make a query which takes the raw values from the table and calculates the field1*field2 values as another field in the resulting recordset. You then design the report based, not on the original table, but on the query you have just saved. The SQL for this query looks something like:
SELECT Table.field1, Table.field2, Table.field1*Table.field2 AS Product FROM Table;
When you open the report, it runs the query and puts the values from the query into the report.

 

[uscitizen]

better late than never folks.....

found a ms knowledge base article (#209172) titled "how to: use the controlsource property to store calculated values in access 2000" - has you create the required field to be computed in your table, create a macro that performs the computation using the 'set value' action which stores the result in the field (appearing on the table)-- all of which lets you create a form upon which the calculated field appears, not to mention a report based upon the self-same form that displays it as well.

check it out.

 

[uscitizen]

as fate would decree, it now seems as though the calculated value will be entered directly into the form.

i deleted the macro performing the calculation and even removed the control on the form and have replaced it with another, however access is sending the following message:

"Microsoft Access can't find the macro 'BSA_Macro'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.

Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under."

can anyone help identify how to turn this 'informational' message off?