Printable View
I want to populate an underlying table (in MS Access 2000)with the results from a calculated field.The main purpose is to store the calculated data for reporting purpose.Currently,I can only get the calculated data at the form level ,but it doesnt stored in the table hence I cant get the correct records through queries.
any help please???
Is this all happening in the same table?
After doing the calculations, have you tried storing the results using an INSERT statement?
Typically, calculations are done on the fly and the results are populated in a report or on a form, but not saved. Is there are specific reason you wish to save the results as opposed to simply rerunning the calculation code?
I agree that calculated values should not really be stored, as the other data may change (and so the calculated values would be wrong). As Access does not support triggers etc you cannot guarantee the validity of the calculated data.
One possible way is to create a temporary table for reporting purposes, into which you Insert the data and the calculated field.
Of course it depends on how complex and time consuming the calculations are. Normally you would simply have a query (or view) that performs the calculations when it is opened, but if that is too slow (the table(s) queried are very large and the calculations complex) then you could have an action query or stored procedure that periodically builds a table with the results of the calculations, so you can read that directly. I did that for the performance gain when users were accessing data from a large database. The views I wrote worked, but when we had the old server it creaked a bit, so I wrote a stored procedure which ran overnight and rebuilt tables which mirrored the output of the views (I also gave the tables the same names as the original views so I wouldn't have to rewrite the frontend code). Not 100% good practice I know, but it wasn't crucial that the data was up-to-the-minute, and it did increase performance. With our new server it's unnecessary though.
The above is about SQL Server BTW. With Access the performance gain would probably be more substantial, but replace 'views' with 'select queries' and 'stored procedures' with 'action queries.'
Thanks Hack& othersQuote:
Originally Posted by Hack
So far I have a simple application composed on a table,a query,a form and a report.A calculated field is on the form and its expression has been defined in its control source property .
Well ,I havent tried storing results using an INSERT INTO statement,however I am using a SAVE command on the form.And I might have more than one record updated and saved the same way.What I really want is to have the report with uptodated records including those of calculated fields...no anyother specific reason of storing those records.Bearing in mind that my report depends on an underlying query,Is it possible to get those updated records by running the report "on the fly"????
si_the_geek,Quote:
Originally Posted by si_the_geek
Could you explain a bit on how to go about your suggestion??? Bearing in mind that my simple application have got a single data entry form,a table,a query and a report.
Thanks
By the way,
Sometimes back, I was told that I should define a calculated field at the query level ,then run the query from the report point of view.I had tried it,it works fine when ran directly as a query.But when ran the same query from the report point of view,the calculated field is being passed as a parameter value ie it asks to enter a calculated field as a parameter value.below is a query example,In this case,ExpectedEarning is a calculated field:
SELECT FDRS.ACTUAL_PYMT_DATE, ([FACE_VALUE]*[INTEREST_RATES]*[DURATION]/365) AS ExpectedEarning
FROM FDRS
WHERE (((FDRS.ACTUAL_EARNINGS)<>0));
When ran as a report, it asks for ExpectedEarning value.
Please help urgently!!!