Query help with calculations 1

[Axoliien]

I am in need of some help with calculations. I have the following set up in a database.

[b]tbl_Orders[/b]
OrderID (AutoNumber)
OrderInfo (Other stuff)
Primary Key (OrderID)

[b]tbl_Inventory[/b]
InvID (AutoNumber)
InvDesc (VarChar 64)
InvPriceFirst (Currency)
InvPriceAddtl (Currency)
Primary Key (InvID)

[b]tbl_Bill[/b]
OrderID (FK, Integer)
InvID (FK, Integer)
Qty (Integer)
PriceFirst (Currency)
PriceAddtl (Currency)
Primary Key (OrderID, InvID)

The Inventory has current prices and the Bill has prices at time of purchase. Now I am trying to build a query that will output the bill for a given OrderID. I need it to output:
InvDesc, Qty, PriceFirst, PriceAddtl, PriceTotal

The PriceTotal is given for each record as
PriceTotal = PriceFirst + (PriceAddtl * (Qty-1))

This is where I am stuck, how do I get the query to output each record and the total price? I put the query together to output the data for each record, but I can't figure out how to calculate the total price in SQL. Please help if you can.

Thanks,
Axoliien

 

[ByteMyzer]

Try:
[tt]
SELECT InvDesc, Qty, PriceFirst, PriceAddtl, DSum("[PriceFirst]+([PriceAddtl]*([Qty]-1))","tbl_Bill","[OrderID]=" & [OrderID]) AS PriceTotal
FROM tbl_Bill INNER JOIN tbl_Inventory ON tbl_Bill.InvID=tbl_Inventory.InvID;
[/tt]

 

[PHV]

And what about something like this ?
SELECT T.InvDesc, A.Qty, A.PriceFirst, A.PriceAddtl, T.PriceTotal
FROM tbl_Bill A INNER JOIN
(SELECT B.InvID, I.InvDesc, Sum(B.PriceFirst + (B.PriceAddtl * (B.Qty-1))) As PriceTotal
FROM tbl_Bill B INNER JOIN tbl_Inventory I ON B.InvID=I.InvID
GROUP BY B.InvID, I.InvDesc) T ON A.InvID=T.InvID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Axoliien]

PHV had it really close and put me on track to get what I wanted. It had been a while since my classes on using the same table twice in a single query, which turned out to output the sum as I needed it. Thanks PHV!