How to combine row with identical column using ms access query (1 Viewer)

John Wink

New member
Local time
Today, 11:15
Joined
Jan 28, 2022
Messages
1
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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:15
Joined
Oct 29, 2018
Messages
18,972
Hi. Welcome to AWF!

Just another version of the same concept. Cheers!

 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:15
Joined
Feb 19, 2002
Messages
36,296
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
Open the query in design view and press the big sigma button. That will add a new line to the query with group by. Change the columns you want to sum to Sum leave the others alone.
 

Users who are viewing this thread

Top Bottom