Concatenate on record in query

[Costefran]

Can anyone help with the following

I am trying to fing a way to group records based on a reference and concatenate the results of a number of sub tables

To try and explain, the select query would like like this

Reference Sub table result
1994 Wash up
1993 Take out dog
1994 Go shopping
1994 drive car
1994 wash car
1993 Get dressed
1995 Drink coffee
1994 Play soccer

I would like to group the records on the reference and concatenate the sub table result so it looks something like this

1993 Take Dog Out - Get dressed
1994 Wash Up - Go shopping - drive car - wash car - Play soccer
1995 Drink coffee

As always any help would be appreciated

 

[lespaul]

take a look at FAQ701-4233

Leslie

 

[Costefran]

lespaul

Apologies but I am a novice regards syntax on this code

I have copied the code into a module, taken out comment marks and I have this but I keep getting a compile error

Here is a sample of the code
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily

and here is whate I have done to it and then recieved a compile error

Docmd.RunSQL "SELECT [FamID],Concatenate(SELECT [FirstName] FROM tblFamMem WHERE FamID =" & FamID as FirstNames FROM tblFamily"

I know I am doing something wrong but not sure what

If you could help it would be appreciated

 

[dhookom]

RunSQL is for action queries. You have to use your table and field names, not tblFamMem and FamID.

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Got it

Many thanks