Is it possible to use a formula in a field to calculate Qty

[mcnaught]

Hi
I'm trying to setup a simple database to test if this idea will work in
Access and have run into a wall.

I have 3 Tables

Client:
(CID), Surname, A, B
1, Smith, 5, 3

Component:
(CompID), Description, Qty, Price, Formula
A001, A1 desc, ,$10, =A
A002, A2 desc, ,$20, =B
A003, A3 desc, ,$30, =A*B

ClientComp:
(CID, CompID), Description, Qty, Price, Formula
1, A001, A1 desc, 5, $10, =A
1, A002, A2 desc, 3, $20, =B
1, A003, A3 desc, 15, $30, =A*B

How can I get the ClientComp table to make use of the formula and
populate the Qty field. Can this be done in a query or a module?

Is there a better way?

 

[Joseph Meehan]

Hi
I'm trying to setup a simple database to test if this idea will work
in Access and have run into a wall.

I have 3 Tables

Client:
(CID), Surname, A, B
1, Smith, 5, 3

Component:
(CompID), Description, Qty, Price, Formula
A001, A1 desc, ,$10, =A
A002, A2 desc, ,$20, =B
A003, A3 desc, ,$30, =A*B

ClientComp:
(CID, CompID), Description, Qty, Price, Formula
1, A001, A1 desc, 5, $10, =A
1, A002, A2 desc, 3, $20, =B
1, A003, A3 desc, 15, $30, =A*B

How can I get the ClientComp table to make use of the formula and
populate the Qty field. Can this be done in a query or a module?

You generally don't save the results of a calculation in a table. It is
better to just recalculate it any time you need it. The exception to this
is where one or more factors may change and you want to record the results
of the calculation as of a specific time based on the values at that time.

You could populate the field using the formula in an update query, or
your could use the formula in a form. What would be best would depend on
your work flow. However see above, it is likely best not to store it at
all.

 

[mcnaught]

I agree but in this case there are a number of factors that "may"
change the quantity. So the calculated quantity is just a starting
point that I want the user to be able to change.

 

[Joseph Meehan]

I agree but in this case there are a number of factors that "may"
change the quantity. So the calculated quantity is just a starting
point that I want the user to be able to change.

I would suggest doing the calculation in a form then. Again at what
point would depend on the specifics of the data and when - how it is
captured.

If it is all being entered at the same time, I would consider locking
and hiding the control for the calculated amount as the default for the
form. Then showing all the required values as usual allow the user to enter
the values. Have a button they can click when they are finished entering
data.

The button then can calculate the total, update the field and display
and unlock the hidden control. You may want another button to unlock the
control if the norm is to not edit the resulting value to reduce the chance
of accidental changes to the calculated amount. I would also consider
locking the controls for the source values to prevent them from being edited
after the calculation was completed as someone will notice an error, go back
and change it and not realize the calculated value was not changed. Of
course you will also need to provide a method of editing those values and
again testing to see if the calculated total needs to be updated.