Join together multiple rows to one Field

[dmax17]

Hello,

I am fairly new to Access and this forum has been very helpful.
I have been struggling with combining multiple rows into one row for an order summary report.

Specifically, I have three tables (Products, Orders, and OrderDetails)

Products
=======
ProductID = AutoNumber
ProductName = String


Orders
======
OrderID = AutoNumber
OrderDate = Date/Time
OrderInfo = Memo
CustomerName = Text
CustomerAddress = Text
PaymentDetails = Text

OrderProductDetails
=========
OrderNumber = Number (Linked to OrderID)
ProductNumber = Number (Linked to ProductID)
Notes = Memo (Notes on product customization)


The OrderProductDetails table is in many to one relationships with both Orders and Products table. If I place an order with 3 products, the OrderProductDetails datasheet would be:

OrderNumber ProductNumber
========================
1 78 (Product 1)
1 89 (Product 2)
1 56 (Product 3)

On my report, instead of 3 entries for this order, I would like show just one entry under a column "Products Ordered" ---> Product 1, Product 2, Product 3.

I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.

Anyhelp would be appreciated.

I am using Access 2010.

Thanks

 

[CJ_London]

I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.

before we recommend the something you've already tried, it would be helpful if you detail which ones you have tried and explain why you can't configure them. For now, I suggest this one

http://allenbrowne.com/func-concat.html

 

[dmax17]

I have put that function in a module in my application. However, I am having trouble using it as I am not very familiar with how to use it.

I have an INNER JOIN query which retrieves information for the report. I tried to embed that function in the query, but I keep getting the "Not enough Parameters" errors.

How do I use that function for this particular situation?

Thanks

 

[CJ_London]

which function? the one I suggested?

And what is your query? i.e. the sql for the query?

and what happens if you remove the function from the query?

 

[dmax17]

which function? the one I suggested?

And what is your query? i.e. the sql for the query?

and what happens if you remove the function from the query?

Yes, the function you suggested. I am trying to use that as an expression in the report field.

ConcatRelated("ProductName", "Products", "[Products]![ProductID]=" & "[QueryBuildOrdersAndProducts]![ProductNumber]")

I hope this helps.

 

[dmax17]

This is the error I get from report when I try to run it.

---------------------------
ConcatRelated()
---------------------------
Error 3061: Too few parameters. Expected 1.
---------------------------
OK
---------------------------

 

[CJ_London]

so this is to populate the value in a control

it looks like the issue is with

"[QueryBuildOrdersAndProducts]![ProductNumber]"

1. you don't need the quotation marks
2. if product number is in your report you refer to the report properties, not the recordsource queries - i.e. you just need [ProductNumber]

 

[CJ_London]

also - if this is the controlsoruce for a control then you need an equals sign

=ConcatRelated("ProductName", "Products", [ProductID]=" & [ProductNumber])

 

[dmax17]

I actually moved the function to the query below the form. I get the same error.

ConcatRelated("[Products].ProductName","[Products]","[OrderProductDetails].OrderNumber =" & "[Orders].OrderID ") AS Expr1,

I get the error.

---------------------------
ConcatRelated()
---------------------------
Error 3061: Too few parameters. Expected 2.
---------------------------
OK
---------------------------

I doubled checked the syntax of the SQL Query and everything else looks fine.

 

[CJ_London]

you have made the same error here

"[Orders].OrderID "

as I advised here

1. you don't need the quotation marks

 

[dmax17]

you have made the same error here


as I advised here

Thanks it worked !!