make-table query / concatenate SQL help

[ossse]

I am trying to build my own table and incorporate the 'concatenate' function (found in FAQ701-4233: How to concatenate multiple child records into a single value).

My two original table look as follows:

Table: Tasks
taskCEID taskID
1 6
1 6
1 4
1 6
3 2
3 1
3 2

Table: TaskCauses
taskCauseTaskID taskCauseFailureCause
6 a
6 b
4 c
4 d
4 e
2 f
1 g
1 h

I want to make the following Table
Table: Test
CEID taskID2 Cause
1 6 a, b
1 4 c, d, e
3 2 f
3 1 g, h

When I physically build the 'make-table query' myself it works... so I copied the SQL from that table into the VBA and I am getting an error.

Here is my line of code:

DoCmd.RunSQL "SELECT Tasks.taskCEID AS CEID, Tasks.taskID AS taskID2, Concatenate("SELECT taskCauseFailureCause FROM TaskCauses WHERE taskCauseTaskID=""" & [taskID]) AS Cause INTO test2 FROM Tasks ORDER BY Tasks.taskCEID, Tasks.taskID;"

The error that I am getting is "compile error: expected end of statement". and then the bolded 'SELECT' gets highlighted.

Any Ideas? Thanks

 

[genomon]

("SELECT taskCauseFailureCause FROM TaskCauses WHERE taskCauseTaskID=""" & [taskID])

Try changing this to:
taskCauseTaskID="'" & [taskID] & "'")

FYI those are single quotes enclosed by double quotes....

I wondered why the baseball was getting bigger.
Then it hit me.

 

[ossse]

Thanks for the response, but I just figured it out.

This works too!

DoCmd.RunSQL "SELECT taskCEID AS CEID,taskID AS taskID2,Concatenate('SELECT taskCauseFailureCause FROM TaskCauses WHERE taskCauseTaskID=' & [taskID]) AS Cause INTO test2 FROM Tasks ORDER BY taskCEID,taskID;