Query Formulas

[ShadeRF]

In my database now I currently have a user input a Retail Price then run a query which calculates all of the following:



Retail : Entered Manual
SellTo: Format(Nz(([Retail])*([PackSize]))/1.3,"Currency")
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
DistMargin: Nz([SellTo])-Nz([LICDistributor])
MarginPerc: Nz([DistMargin])/Nz([LICDistributor])

However I'd also like to do the reverse and allow them to enter the
DistMargin or MarginPerc and it basically reverse calculate all the fields including the retail which I previously entered manually. How can I do this?

[hertfordkc]

will calculate the others and update the record?
If so, how about using the afterupdate event to run the VBA to do the calcs and post to the other fields?

[ShadeRF]

My issue is more so how to change the formulas so it works

[ShadeRF]

Anyone have any input?

[hertfordkc]

other sources. I don't want to try to figure it out when I'd be guessing at which fields have values and which are unknown. I understand that Retail, Marginperc, and DistMargin are unknown, as is LicDistributor. Are all of the other fields known?

[ShadeRF]

Right now only retail is unknown. I key in the retail and all the formula's listed process. What I want to do is have one where only the DistMargin is the unknown and process the others based on this. And then do the same seperately for Margin% being the unknown.

[hertfordkc]

Case Retail:
Retail : Entered Manual
SellTo: Format(Nz(([Retail])*([PackSize]))/1.3,"Currency")
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
DistMargin: Nz([SellTo])-Nz([LICDistributor])
MarginPerc: Nz([DistMargin])/Nz([LICDistributor])

Case MarginPerc
MarginPerc: Entered Manual
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
DistMargin: MarginPerc*NZ([LICDistributor])
SellTo: DistMargin+Nz([LICDistributor])
Retail: SellTo*1.3/[Packsize]

See if MarginPerc works. If it does, I'll do the other one. Note: You may need to reinsert NZ in the equations to stop some errors.

[ShadeRF]

I got this in and it appears to be working beautifully! Thank you very much. I'd love the other one if you can do it. Thanks again!

[hertfordkc]

[QUOTE=hertfordkc;93307]Case Retail:
Retail : Entered Manual
SellTo: Format(Nz(([Retail])*([PackSize]))/1.3,"Currency")
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
DistMargin: Nz([SellTo])-Nz([LICDistributor])
MarginPerc: Nz([DistMargin])/Nz([LICDistributor])

Case MarginPerc
MarginPerc: Entered Manual
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
DistMargin: MarginPerc*NZ([LICDistributor])
SellTo: DistMargin+Nz([LICDistributor])
Retail: SellTo*1.3/[Packsize]

Case DistMargin
DistMargin: Entered Manual
TotalCostImporter: Nz([FOBImporter]+Nz([TaxDuty])+Nz([OceanFreight])+Nz([CBFees])+Nz([InlandFreight])+Nz([MarineInsurance]))
LICDistributor: Nz([FOBDistributor])+Nz([Freight])+Nz([TotalTax])
WISNetProfit: Nz([FOBDistributor])-Nz([TotalCostImporter])
MarginPerc = DistMargin/NZ([LICDistributor])
SellTo: DistMargin+Nz([LICDistributor])
Retail: SellTo*1.3/[Packsize]

Note: You may need to reinsert NZ in the equations to stop some errors.