Calculate a value from 2 fields to a 3rd field in same table. 1

[dkmidi]

Hi there!

I set up 3 fields: wage1, wage2 and avgWage.
How and where can I set a formula up so that the avgWage field will automatically calculate the avg value for wage1 and wage2? All three fields are in the same table.

Is this possible?

I've been trying to stick an expression in the default value section of the field but can't seem to get it to work.

Any help would be great! Please give sample syntax too!

Thanks!

Derek

 

[Darrylles]

Hiya,

I think you're expecting a database table to be like a spreadsheet.

I'll never say 'never' (again), but I don't think that you can enter expressions or formula's in a database field like you can with a cell in a spreadsheet.

The way this would normally work is: you have a form with your 3 fields on it (or 2 if you don't want the average field to be seen). After the user has entered values in both fields, then you use VB code or a macro to calculate the average and place the result in your 'avgWage' field.

If you already have a large number of records with wage1 and wage2 values, then you would create an update query to update all 'avgWage' fields in each record.

Regards,

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[EndoDoc]

you can't make calculated fields in tables, but you can in Queries! Create a query based on your table, and in a blank field, just type "=(wage1 + wage2)/2" (or something like that. The field will automatically be named Expr1:.... You can then rename Expr1 to whatever you wish. Then you must base your forms and reports on the query, not the table. Hope that helps.

 

[alr0]

Hi dkmidi,

To expand the previous replies, creating this third field in a table would denormalize your data and should be avoided. You can calculate this average anytime you need it as mentioned in a query for a form or report or in a control directly.

This creates all kinds of future problems if wage1 or wage2 get updated or corrected and storing the calculated value serves no purpose.

If you still think you need this, I would be happy to help find a better solution with a little more detail.

Hoping this helps,

al

 

[dkmidi]

Thanks all! All your help has been very useful... a query calculation it is then.

Thanks again!

DK