Record Values made by Product of 2 other fields

[Only1Abhi]

Hi People!

I posted this problem years ago!
I searched for the thread but couldn't find it!


So... I'm posting it again!
Hoping access professionals can help out!


---------------------------------------------SCENARIO-------------------------
I have a table called tblTransaction.
In the tables, there are many fields but 3 in question right now:


Amount
Qty
Total



----------------------------------------------PROBLEM-------------------------
I want the Total field's value to be automated. It should depend on the Amount and Qty fields like this:

Total = Amount * Qty


I want this value to be stored in the table tblTransaction and NOT JUST viewed on my form frmTransaction.




Can anyone please help?
I'm sure there is a solution but can't remember how!

Many thanks!
Regards,
Abhi!

 

[Mile-O]

Have a hidden textbox on your form bound to the Total field. (as an example, I'll call it txtTotal)

You have a textbox with the calculation: I'll call that txtCalc

In the BeforeUpdate() event of the form:

Me.txtTotal = Me.txtCalc

 

[Rich]

Of course you shouldn't store a calculated value in this way

 

[Mile-O]

Of course!

 

[Only1Abhi]

if not that way....

Then what way?

 

[Mile-O]

He means you shouldn't be storing calculated values at all.

 

[Only1Abhi]

Hmmm... Why is that a problem for my database?

I mean...
If the computer automatically did the calculation for me... It would save me lots of time...

And I need the value to be permenant anyway!

What should I do instead then?

Many thanks!

 

[Only1Abhi]

And People:

I may need to change the Amount or Qty fields...

But when I change any of these two, I need the field Total to automatically change INSTANTLY!

Is this possible?

Thanks!

 

[Mile-O]

• it's a problem as it violates 3rd Normal Form (you should ot store calculated values)
• you have the price, you have the quantity, therefore you can calculate the total at any time;
• if you stored the total and changed the quantity or the price then you would have to recalculate the value in the table (doubling your work) and if you forget to recalculate you then have incorrect values in your table and which one is incorrect? The quantity, the price, or the total?
• the computer won't automatically do the calculation for you - the idea of having an expression in a table is something people should epect in Excel; not Access;
• consider a further field in your database called DateExpired and, when a price changes, add a new record with the same details and change the old price's DateExpired to the date it no longer is used - this ensures that totals will always be accurate without storing a calculated value
• 

 

[Only1Abhi]

Mile, I can now understand some of the problems that occur with this method.

But I'm still confused with some things you said!

Can u please tell me...

When I change the Amount or Qty value,
Although the Invisible textbox ("txtCalc") updates,
How do I get the visible textbox ("txtTotal") to update automatically?


If what I want is not possible, what's the alternative?

Kind Regards,
Abhi

 

[Only1Abhi]

Can anyone please help me?
I really need a solution urgently!

Many thanks to everyone!

Regards,
Abhi

 

[Only1Abhi]

Thanks for the help Pat!

1 quick question please...

Bind the calculated field to a control

How do I do that?
I mean I looked up Access Help and other websites. But it still doesn't work. That means I must be doing it wrong.


Can you please explain How I can bind the calculated field to a control please. Does this mean I won't need the field "Total" in my system?

Many thanks!

P.S. I need the calculated field's value to be stored permenantly on tblTransaction's "Total" field please.

 

[Only1Abhi]

just trying to move this back up the queue to get noticed.

 

[Only1Abhi]

ok I solved this problem.

In all three textboxes on the form:

Amount
Qty
Total


I entered in each textbox's AfterUpdate() Command:

DoCmd.RunCommand acCmdSaveRecord

And then.......

BOOOOOMB!

Problem Solved.

Thanks anyways people.

P.S. Does this method have any disadvantages?

 

[namliam]

P.S. Does this method have any disadvantages?

Yes Many, to many to list starting with Mile's summation above...

You are breaking one off THE most important rules of databases:
THOU SHALT NOT STORE CALCULATED VALUES

Exceptions to the rule MUST excist, but you must have a darned good reason to break this commandment.
THOU SHALT NOT STORE CALCULATED VALUES

I dont have a clue as to where you got the solution you now have but your not listening to the (good) advise from this forum!!
THOU SHALT NOT STORE CALCULATED VALUES

To conclude, re-read this entire thread (posts not from you), the read it again. Go and take a walk. Read this thread again, then again before you leave home and again tomorrow before and after the coffie break. Then try and implement Pat's solution.

Regards

The Mailman

P.S. I allmost forgot: THOU SHALT NOT STORE CALCULATED VALUES

 

[Mile-O]

mailman, you forgot to mention that THOU SHALT NOT STORE CALCULATED VALUES.

 

[Only1Abhi]

THOU SHALL NOT STORE CALCULATED VALUES

I don't understand what the problem is with storing calculated values..

But I'll take your word for it!

And I didn't understand Pat completly...

Can anyone please explain this part clearly please (step-by-step):

Bind the calculated field to a control

Many thanks!
Regards,
Abhi

P.S. THOU SHALL STORE THOU CACULATED VALUES IF THOU DOESN'T FIND ANY OTHER THOU SOLUTIONS FOR THOU! (only jokin there)

 

[Mile-O]

I don't understand what the problem is with storing calculated values..

...because the TOTAL is not DEPENDANT upon the table's key.

...it's a violation of THIRD NORMAL FORM!

LOOK!

 

[Only1Abhi]

ok yeh!
I understand that now!
I learnt it in skool aswell
1NF, 2NF and then 3NF

Thanks for that!

But I don't understand that quote by Pat on my last post.

I mean I've even looked in F1 help but it's kinda confusing for me.

Can you please explain it to me?


Kind Regards,
Abhi

 

[Mile-O]

She just means: put the calculation in a textbox's ControlSource.

i.e.

=[txtQuantity] * [txtPrice]