Daturk Daturk - 7 months ago 8
SQL Question

sql - calculating the price of an order and sum of all orders on a receipt

I have three tables as follows:

MenuItem

MenuItemID ItemName ItemPrice
X0001 LatteSmall $15
X0002 LatteBig $18
X0003 MochaSmall $16


Orders

OrderID MenuItemID ReceiptID`
O000001 X0001 R000001
O000002 X0002 R000001
O000003 X0001 R000002
O000004 X0003 R000003


Receipt

ReceiptID ReceiptPrice
R00000001 ???????????
R00000002 ???????????
R00000003 ???????????
R00000004 ???????????


What I am trying yo do is: Calculate the price of each order, and then sum up orders that belong to each receipt. List the summed up values in the
ReceiptPrice
field on the
Receipt
table.

How do I do this with a single query on Microsoft Access 2010?

Any help is appreciated =))

Answer

From the sample data you have provided it's a little hard to tell, but I assume that each receipt may contain multiple orders.

The following query will return each order on each receipt with a total menu item cost per order. This assumes that ItemPrice is numerical, and doesn't actually contain the '$' character, maybe you could confirm that?

SELECT O.ReceiptID, O.OrderID, SUM(MI.ItemPrice) AS TotalReceiptPrice
FROM Orders AS O INNER JOIN MenuItem AS MI ON O.MenuItemID = MI.MenuItemID
GROUP BY O.ReceiptID, O.OrderID

If you wish to only see the total for each receipt, including multiple orders, then try this;

SELECT O.ReceiptID, SUM(MI.ItemPrice) AS TotalReceiptPrice
FROM Orders AS O INNER JOIN MenuItem AS MI ON O.MenuItemID = MI.MenuItemID
GROUP BY O.ReceiptID
Comments