Combining multiple dates on one record 1

[Layth]

I have two tables this is the content

Table no. 1 name: New Primary Table

AMPORDERFK, CompanyName, City
1 SCG Carson
2 MMN Hubbard
3 Staford Crystal
4 Miller Newark


Table no. 2 name: Rundatetable

AMPORDERFK, Rundate
1 11/20/03
2 06/05/04
1 01/01/01
3 09/21/04
4 07/30/80
2 06/22/81
3 08/30/93
4 04/29/84
2 07/05/00


The query I'd like is this with the dates, concatenated, separated by commas

Company, City, Dates
SCG Carson 11/20/03, 01/01/01
MMN Hubbard 06/05/04, 06/22/81, 07/05/00
Staford Crystal 09/21/04, 08/30/93
Miller Newark 07/30/80, 04/29/84


Here is my code:

SELECT [New Primary Table].AMPORDERFK, [New Primary Table].CompanyName, [New Primary Table].City, 
Concatenate("SELECT [Rundatetable].Rundate FROM [Rundatetable] WHERE [Rundatetable].AMPORDERFK =" & [AMPORDERFK] & " ORDER BY [Rundatetable].Rundate") AS DatesRun
FROM [New Primary Table];

Any help will be greatly appreciated.

Thanks,

Layth

 

[Layth]

I'm sorry the error message that I get is
"Undefined function 'Concatenate' in expression"

again any help is always appreciated,

thanks,

Layth

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Layth]

Thank you very much, this is exactly what I needed!

Layth