Saving calculated values on a form into a field in a table

[marcfl]

Hi,

I have an orders form and for every order placed on the form the total cost is calculated, i need to to save the calculated value into a table. Ive looked through like 100 posts and read the FAQs but couldnt find any answers, which is wierd because i would have thought this would be a question frequently asked unless there's a rediculously obvious answer that ive missed!

thanks

Marc.

 

[marcfl]

I forgot to mention that i tried creating a query to calculate the totals through the products selected for each order, but seeing as the price is sometimes changed on the orders form for different clients it was inaccurate.

 

[fneily]

Yes, there is a "rediculously obvious answer". You don't save calculated fields in tables. That is against the protocols of a relational database such as Access. See:

Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[fneily]

Your second post wasn't posted before I responded. Please post your table structures (fieldname, primary key). You should have a table that has a companyID and their discount rate(or whatever you call it).

 

[marcfl]

My relationships structure can be seen in the link i pasted.

 

[fneily]

A couple of things I'd change in your tables:
You have Unit_Price in two tables. Should only be in the Products table. Trivial.
You have 4 comment fields in the Order table. Against the first normal form. Either just have one field, or have a Comments table.

If I understand what you want, then in the query design pane, add the Order_Details table. Bring down OrderID into the grid. Click the Totals button(the Greek E).
In the second cell of the Field row, next to OrderID, type:
Total_AfterDisc: Sum(([Unit_price]*[Quantity]*(1-[Discount])/100)*100)

For the Total row, under OrderID, Select GroupBy.
For the new field, select Expression

 

[marcfl]

I have UnitPrice in 2 tables because when i change the price of a product on the Orders form the value gets saved in the [Order Details].[UnitPrice] so it doesnt change the original value.
Those 4 comments fields are for different things i should have named them better i suppose but nm wasnt sure what i was doing when i started.
I fixed what i wanted to do myself a while ago hehe cause i remembered that i had those "modified prices" saved in the Order Details table.. the only problem i have now is as follows:

total: Sum(CCur(((([Order details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*17.5)/100)*100)+[Order details].[UnitPrice]*[Quantity])

The 17.5 is the VAT and this expression works perfectly for all the orders except for one which has no VAT (it adds it to the total of that order). When i was first testing I was using [VAT] instead of the 17.5 but i realised that it just ruined the totals of all the orders.

Any suggestions? I'm sure ill find a way around it eventually tho. Sorry my brain is all over the place atm i need to go for a ciggie. *sigh*

Marc.

 

[fneily]

Since I don't know what VAT is/means, can't VAT be 17.5 for all the orders except one and then its' VAT would be 1?

Still don't understand the two Unit_prices. A product has an original price and that price is then offset by the discount. Or do you change prices on the fly?

I don't understand your formula. It seems you get a discounted total and then add on the original total. But you say it "works perfectly" so I'll presume you know what's going on.

 

[marcfl]

vat is "Value Added Tax" government implemented stuff... but some of our deals will have no VAT.

haha just fixed it while i was typin this reply! just needed to replace the 17.5 with [VAT] and then take away the /100 after it. sweeeeeeeet!

 

[marcfl]

ooh, forgot to say thanks for the help mate its much appreciated

Cheers

Marc.

p.s. i'm in the UK not still workin at 3:50 at night

 

[fneily]

Seems I didn't help that much. You worked through the Total's problem pretty much yourself.

However, the more important part is that you don't save totals.