Hello, I am a worker in a shop and we are exploring MS access so we can use it to store our business transactions. I am not into programming so I am having a hard time understanding the queries.
These are the content of my table:
Customer Table:
--------------------------
CusomerNo(pk) Name
1 John Doe
--------------------------
Order Table:
----------------------------------
OrderNo(pk) CustomerNo(fk)
1 1
----------------------------------
OrderLine Table:
----------------------------------------------------------
OrderNo(fk) ProductNo(fk) Quantity TotalPayment
1 1 2 $20
1 2 1 $5
----------------------------------------------------------
Product Table:
-----------------------------------
ProductNo(pk) ProductName Price
1 Coffee $10
2 Ice Tea $5
------------------------------------
So far, using query wizard, I was able to create a query that joins Customer, Order, OrderLine and Product table.
This is the query:
SELECT Orders.OrderNo,Orders.ProductName, Orders.Quantity, [Price]*[Quantity] AS TotalPayment
FROM (Customer INNER JOIN [Order] ON Customer.[CustomerNo] = Order.[CustomerNo]) INNER JOIN Orders ON Order.[OrderNo] = Orders.[OrderNo] ;
My problem is, I do not know how to 1.) combine orders that have the same OrderNo 2.) concatenate the order's ProductName 3.) add the Quantity and TotalPayment of both order
To be specific this is how it currently looks like:
-------------------------------------------------------
OrderNo ProductName Quantity TotalPayment
1 Coffee 2 $10
1 Ice Tea 1 $5
-------------------------------------------------------
This is how I want it to be:
-------------------------------------------------------
OrderNo ProductName Quantity TotalPayment
1 Coffee, Ice Tea 3 $25
-------------------------------------------------------
Edit
_______________________________________
I tried ConcatRelated
SELECT Orders.OrderNo, ConcatRelated("[Orders.ProductNo]", "[Transaction]", "[Orders.OrderNo]" = " & [Orders.OrderNo]) AS Product, SUM(Orders.Quantity) AS TotalItems, SUM([Price]*[Quantity]) AS TotalPayment
FROM (Customer INNER JOIN [Order] ON Customer.[CustomerNo] = Order.[CustomerNo]) INNER JOIN Orders ON Order.[OrderNo] = Orders.[OrderNo]
GROUP BY Orders.OrderNo;
but it gives me a syntax error, also i am not sure on what to put in the second argument.
These are the content of my table:
Customer Table:
--------------------------
CusomerNo(pk) Name
1 John Doe
--------------------------
Order Table:
----------------------------------
OrderNo(pk) CustomerNo(fk)
1 1
----------------------------------
OrderLine Table:
----------------------------------------------------------
OrderNo(fk) ProductNo(fk) Quantity TotalPayment
1 1 2 $20
1 2 1 $5
----------------------------------------------------------
Product Table:
-----------------------------------
ProductNo(pk) ProductName Price
1 Coffee $10
2 Ice Tea $5
------------------------------------
So far, using query wizard, I was able to create a query that joins Customer, Order, OrderLine and Product table.
This is the query:
SELECT Orders.OrderNo,Orders.ProductName, Orders.Quantity, [Price]*[Quantity] AS TotalPayment
FROM (Customer INNER JOIN [Order] ON Customer.[CustomerNo] = Order.[CustomerNo]) INNER JOIN Orders ON Order.[OrderNo] = Orders.[OrderNo] ;
My problem is, I do not know how to 1.) combine orders that have the same OrderNo 2.) concatenate the order's ProductName 3.) add the Quantity and TotalPayment of both order
To be specific this is how it currently looks like:
-------------------------------------------------------
OrderNo ProductName Quantity TotalPayment
1 Coffee 2 $10
1 Ice Tea 1 $5
-------------------------------------------------------
This is how I want it to be:
-------------------------------------------------------
OrderNo ProductName Quantity TotalPayment
1 Coffee, Ice Tea 3 $25
-------------------------------------------------------
Edit
_______________________________________
I tried ConcatRelated
SELECT Orders.OrderNo, ConcatRelated("[Orders.ProductNo]", "[Transaction]", "[Orders.OrderNo]" = " & [Orders.OrderNo]) AS Product, SUM(Orders.Quantity) AS TotalItems, SUM([Price]*[Quantity]) AS TotalPayment
FROM (Customer INNER JOIN [Order] ON Customer.[CustomerNo] = Order.[CustomerNo]) INNER JOIN Orders ON Order.[OrderNo] = Orders.[OrderNo]
GROUP BY Orders.OrderNo;
but it gives me a syntax error, also i am not sure on what to put in the second argument.
Last edited: