Field Calculation help needed.

[Tremor]

Hi all - this is my first post here

I need to calculate a field based upon two other fields, but not quite sure how to do this.

The situation is I have a form based on a table called SalesOrders, and a table called PurchaseContracts.

I need to work out a field called Offtake in the PurchaseContracts.

Basically every time a new order is enterd in SalesOrders, it has a ProductCode which is selected from a list pulled from the PurchaseContract table then I enter the amount for OrderInTonnes.
What I need to do is have the Offtake field provide a total of all the OrderInTonnes based on the ProductCode.

So each ProductCode could have any amount of orders in the SalesOrders, and I need the Offtake to add up all the OrderInTonnes for each ProductCode.

To sum up, if the ProductCode from SalesOrders matches the ProductCode from PurchaseContracts, the OrderInTonnes from SalesOrders would be added up and the total going into the Offtake field.

I'm imagining that I could use the IIF function?

I know that it's not usual practice to store calculated values, but the person I'm doing this for wants it like that!


Cheers, Paul

 

[RuralGuy]

Have you tried doing the calculation in a query?

 

[ajetrumpet]

Have you tried doing the calculation in a query?

your answers are getting about as short as mine are allan.

 

[Tremor]

I was told on another forum that doing this in a query would be the best way, but I really do need to store this in a table if possible. Any ideas on what the calculation would be?

 

[jsv2002]

To sum up, if the ProductCode from SalesOrders matches the ProductCode from PurchaseContracts, the OrderInTonnes from SalesOrders would be added up and the total going into the Offtake field.

If Me.ProductCode = (Whatever you want could be another control or table lookup) Then

Me.Offtake = [OrderInTonnes] <<(Don't know where this value is held or calculated?)

End If

This could go in the after update of your form ProductCode its just an example as I don't know where your values are stored or how calculated.

good luck John

PS. I store calculated values for historical purposes (such as price or tax changes) but don't see the relevance here.

 

[RuralGuy]

Maybe you will find this link useful. BTW, if a friend asked you to design a car *without* breaks, would you be doing them a favor to do so?

 

[Tremor]

Ok, if I was to do this as a query, any ideas on the best way to get the result?
I would have fields from 2 tables - PurchaseContracts and Sales Orders. Both tables have a field called PurchaseContract, and everytime I enter a new order in SalesOrder I choose a PurchaseContract from a list which pulls it from PurchaseContracts. I then enter the OrderInTonnes for that order. I need to know the total OrderInTonnes that each PurchaseContract has on the SalesOrders table. This would be known as Offtake.

 

[Rabbie]

I know that it's not usual practice to store calculated values, but the person I'm doing this for wants it like that!


Cheers, Paul

Welcome to the forum

The main reason for not storing calculated values is that if any of the values used in the calculation change then you need to recalculate the value. As Access does not have a trigger event this can mean you need a lot of code to make sure the stored value is accurate. Much better to calculate the value when you need it

 

[RuralGuy]

Can you create a query that joins the PurchaseContracts and Sales Orders tables on the PurchaseContract field? Does it include all of the records you need?

 

[Tremor]

Thanks for all the help everyone. I've managed to get it doing what I want now. Everything works on my system perfectly - the problem is when I sent it to the person who it's for, he keeps getting errors everytime he updates a control on a form that is using code from the AfterUpdate event. This happens on every form he tries. He has macros enabled - it won't even do a simple calculation like adding two numbers from diffferent fields together such as NetValue and VAT. Any ideas on this?

 

[jsv2002]

Could you tell us what errors they are getting? Could be anything from their local settings to reference settings so any information you could provide would help.

 

[RuralGuy]

Is the person using ac2007 and is it in a trusted location?

 

[Tremor]

I've now done that and also eventually found that the Has Module property was set to off on all his forms, which I'm guessing wasn't helping. Anyway, all is now fine!

 

[RuralGuy]

That's great! Thanks for posting back with your success.