Recording Calculated field

hayashig

Registered User.
Local time
Today, 03:09
Joined
Jul 1, 2010
Messages
28
I haven't asked a question here in a while so I hope I am not asking too much.

Currently, I am using MS Access 2007.

My problem is that I have a calculation in my form of which the variables are also recorded on a table, but I also need the calculated value to be recorded on the table. I know recording a calculated field is discouraged, but for my access, the each record will only have one value which will never change, so it won't cause any problems down the line.

As an example, lets say there are 4 fields on the table;
1. Total ABC
2. A
3. B
4. C

On the form, there are 4 fields same as the table, but A, B and C are directly recorded onto the corresponding table field, and Total ABC calculates the sum of A, B and C then records it to its corresponding table field.

If at all possible, can someone make a working example MS Access document since my understanding of how this is done is not up to par?
 
why would you need to store a calculated field?

Rule #1, #2 and #3 say, we do not store calculated values, rather we recalculate it on the fly when ever needed.
 
Sometimes it is VERY necessary to save a calculated field. For example, an invoice, where your markup and item price WILL change over time.
In one DB I have I use ItemPrice and ItemMarkup in a table for "OrderDetail" and "UnitCost" and "Markup" in the table for Items.
When you choose an item from a drop down the code below saves the current cost and markup to correct fields.


Code:
Private Sub cboItemID_AfterUpdate()
   On Error GoTo cboItemID_AfterUpdate_Error

Me.ItemPrice = Me.UnitCost
Me.ItemMarkup = Me.Markup

'this copies current price and markup to the order detail table,needed to maintain history

   On Error GoTo 0
   Exit Sub

cboItemID_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboItemID_AfterUpdate of VBA Document Form_frmOrderDetailSubform2"
End Sub

Hope that helps.
 
UnitCost is not a calculated value, though sometimes it is copied, other times the product is stored with a price period.

A pen will cost 99 cents period 01-01-2010 to 31-08-2010
1.05 euro 01-09-2010 to now

Then the invoice will reverence that and fetch 99c or 1.05 depending on the invoice creation date or order date.

Same can be true for Markup, VAT, Customer discount, Action discount, etc etc...

Very very rarely do you actually need to store the value, sometimes done for convenience on complex calculations or heavy calculations (lots of records/i.o./cpu/memory/time) involved... but rarely very rarely

Surely a simple calculation a+b+c or some simular should not be store in a seperate field.
 
Thanks for the replies. I am not doing any heavy calculations nor am I doing any calculation that will ever change. Its just that I would like to store the numerical values and the calculated value to later organize the data by records.

I am horrible at understanding codes, so is it possible to give me an example using the above Total ABC, A, B and C as the fields?
 
Well, ALL of the accounting systems I have worked with store an invoice amount...there are too many variables and instances that may arise, none of which are date dependent....You store a calculated field for "Historical" purposes when needed....
Yours on the other hand will not change...... therefor it doesn't need to be, and as Namlian said, shouldn't be.
But IF you need to, simplest to do it in the query.... Just add a field in your query grid... D: nz([A])+nz()+nz([C])

I think the syntax is correct on that...someone chime in if it isn't.....
 
Thanks all for the replies.

I guess my reasoning for keeping the calculated values seems odd, so maybe i can try to explain it more straight forward. I want to store the calculated value on the table so that later I can use the "Analyze Table" function to look up the data.

So here is where I am so far. I have form fields "BMI","Height", "Height1", "Height2", and "Weight". Of those fields I need to store "BMI", "Weight" and "Height" which already has the control source to its table field. The "Height1" and "Height2" stand for Height in Feet and Inches respectively that is used to calculate the total height in Inches ("Height") which is then used to calculate the BMI.

This is the coding I have so far, but i cant seem to get the "BMI" or "Height" when i fill out the "Height1", "Height2" and "Weight"

Private Sub BMI_AfterUpdate()
Me.BMI = (703 * Me.Weight) / ((Me.Height) ^ 2)
End Sub

Private Sub Height_AfterUpdate()
Me.Height = (12 * Me.Height1 + Me.Height2)
End Sub

Private Sub Height1_AfterUpdate()
Call Height_AfterUpdate
End Sub

Private Sub Height2_AfterUpdate()
Call Height_AfterUpdate
End Sub

Private Sub Weight_AfterUpdate()
Call Height_AfterUpdate
End Sub
 
I want to store the calculated value on the table so that later I can use the "Analyze Table" function to look up the data.

The Analyze Table wizard from Access is not for looking up data. It is for checking whether your structure is correct and may need to be split up into other tables.

In case you weren't aware - you can use Queries in approx 99.9% of the places you can use a table.
 
I finally understood to use the Query for calculation but I am having trouble just adding the values from different form fields. When i do

E: [A]++[C]+[D]
A=1
B=2
C=3
D=4

then all i get is 1234 rather than 10.
 
Your fields are being treated as text. What are their data types? If you can't change them to numeric types, you'll need to wrap each in the appropriate conversion function (CInt, CDbl, etc).
 

Users who are viewing this thread

Back
Top Bottom