Calculation Query

[Matthew Loraditch]

I have two tables, one PO Table, that contains information for each Purchase
Order
I have another table called POs Items, which contains the items for each
purchase order along with quantities and unit prices for each.
I have a field in my PO table that i would like to be able to run an update
query and place a purchase total for each PO into it
When i print POs onto a report I use this equation and get the correct
numbers:
=IIf([Tax]=0,0,0.05*Sum([Quantity]*[Unit Price]))+Sum([Quantity]*[Unit
Price])+[Shipping Charge]

Tax is a yes/no field in PO Table and shipping charge is a number in PO
Table
Quantity and Unit Price are both in POs Items
The tables are related with PO Number being in a one(PO Table) to Many(POs
Items)
I have been dallying with this:
UPDATE [PO Table], [POs Items] SET[Purchase Total] = Sum([Quantity]*[Unit
Price])+[Shipping Charge]
WHERE [PO Table].[PO Number]=[POs Items].[PO Number];
but i get this:
You tried to execute a query that does not include the specified expression
"Sum([Quantity]*[Unit Price])+[Shipping Charge]"
as part of an aggregate function. (Error 3122)
I also have no idea how to calculate the tax, as you can see i used an iif
statement in the report output, but i'm not sure as to how to put that into
SQL.
If it makes it any simpler i have a query where i create the subtotals for
each purchase:
SELECT [POs Items].[PO Number], Sum(CCur([Unit Price]*[Quantity])) AS
Subtotal
FROM [POs Items]
GROUP BY [POs Items].[PO Number];
Hopefully someone understands all my jibberish and can help ! :-D
Thanks in advance!

 

[Steve Schapel]

Matthew,

My only advice on this is - Don't do it. You have already worked out
how to get the PO total onto your report. The same principle would also
apply to your forms, wherever this value is required. In a database,
this is the correct way to do it, and you already have that sorted.
Storing this figure in a table is neither necessary nor desirable, and
just adds difficulty and complexity for no purpose.

But just so my reply isn't all negative here is a simplified
version of your calculation...
=Sum([Quantity]*[Unit Price])*(1-[Tax]*0.05)+[Shipping Charge]