CODEWITHSUNDEEP
sqlms-access
doomfront
doomfront

Reputation: 3

Multiple values in one cell from select query

I'm trying to do a select query to show values from two tables. Going to use "Customers" and "Orders at an example. In customers table it just has CustomerID (primary key) and CustomerName (no duplicates). The Orders table shows CustomerName (foreign key) and Order#.

When a customer has multiple orders in the Order table it shows like this

Jon - Order1
Jon - Order2
Luke - Order1
Luke - ORder2
Luke - Order3
Mark - Order1

How can I get the select query to show it like this

Jon - Order1, Order2
Luke - Order1, Order2, Order3
Mark - Order1

It doesn't have to be separated by commas. A stack field will work too. My end goal is to have an "Orders form" that lists each customer with all of their orders in a single "order" field in the form/

Example of the form will be a textbox with the CustomerName and another textbox to list every Order

Current SQL

SELECT tblCustomers.CustomerName, tblOrders.Order#
From tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerName = tblOrders.CustomerName

Upvotes: 0

Views: 223

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

MS Access doesn't natively offer this functionality, therefore, you'll need to use a VBA function similar to Allen Browne's ConcatRelated function.

Applied to your example, you could use this function in the following manner:

select
    t.customername, 
    ConcatRelated("[order#]", "tblorders", "customername = '" & t.customername & "'") 
from
    tblcustomers t

However, I would advise against this representation of data in favour of using a subform.

Aside, it's really odd that your tables are linked on the Customer Name as opposed to the ID.

Upvotes: 1

Related Questions