Calculating Fields in a form and having them recorded in the associated table

[manix]

Dear all,

I have the following form that records tooling data in a tooling data table. I have the following fields:

-Tool Life TOTAL (in parts) (entered by the users)

-Tool Life to Refurbishment (in parts) (entered by the users)

-Monthly Quantity of parts off of the tool (estimated) (entered by the users)

-Month of 1st Shipment (date mm/yyyy format) (entered by user)

from these fields entered by the user the following fields are calculated using the expression builder:

-Months to Retool

-Months to Refurbishment

-Estimated month/year of refurbishment

-Estimated month/year of retool

but I find that by using the expression as the control source for these fields, the calculated values are not recorded in the table. So the records do not contain entries for all of the calculated fields.

How do I get them to appear as values in the table and still be calculated off of the user entered data.

I am no VB whizz and I am just learning queries, so be gentle.

Thanks,

 

[Mile-O]

There is no need to store calculated values in a table. It goes against the rules of database design (or normalisation). Calculations are derived as and when they are needed, whether it be on a form, in a query, or on a report. The fact that you have the calculations appearing on your form shows that you don't need them in the table as every time the form is open the result will be calculated.

 

[manix]

There is no need to store calculated values in a table. It goes against the rules of database design (or normalisation). Calculations are derived as and when they are needed, whether it be on a form, in a query, or on a report. The fact that you have the calculations appearing on your form shows that you don't need them in the table as every time the form is open the result will be calculated.

Thanks SJ,

It does make sense, kind of. I want to query this data though from outside the form. So I need to figure out how to do that. The whole point in having this data calculated is so users can search for tools that need to be refurbished or re-tooled within the next 24,12 or 6 months. I suppose there will be a query to deal with that and so I will have to try and figure out the query to do it.

Thanks for taking the time to reply and help me understand the database approach. Guess I am still thinking in the excel mentality, thinking calculated cells should be recorded and searchable.

 

[CEH]

Manix,
As SJ said.... Don't store it... But you might think about adding your calculations to the query itself... not just an unbound textbox on the form. Just add another field to your grid in your query... Something like.... Total: ([Textbox1] * [Textbox2])..... Then when you do your reports you don't need to rebuild or copy and paste your calculations, they will be there for you already, and you'll have a field named "Total" (in my example) to drop onto your report.