Passing a variable to a sub query

[bernie321]

Hi

I am attempting to return the data from a sub query in this sort of manner:

SELECT tblC.C_ID, (SELECT tblTemp.data
FROM tblTemp
Where C_ID = [Main Query: tblC.C_ID])
FROM tblC

Within the Sub query i want to make all of rows returned from the tblTemp.data to be returned in one value (delimited by commas) on the main query.

Plus the [Main Query: tblC.C_ID] is the variable tblC.C_ID

Any ideas on how i can do this.

Thanks B

 

[PHV]

You may consider write a public function callable from a query:

Public Function GetTempData(ID)
  Dim db As Database
  Dim rs As Recordset
  Dim strData As String
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT data FROM tblTemp WHERE C_ID=" & ID)
' If C_ID isn't defined as numeric: WHERE C_Id='" & ID & "'")
  strData = ""
  Do Until rs.EOF
    If strData = "" Then
      strData = rs.Fields("data")
    Else
      strData = strData & ", " & rs.Fields("data")
    End If
    rs.MoveNext
  Loop
  Set rs = Nothing
  Set db = Nothing
  GetTempData = strData
End Function

Write this function in a code module.
And then your query may look like this:
SELECT C_ID, GetTempData(C_ID) As TempData FROM tblC;


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

 

[bernie321]

Hi

Thanks for the post.

I ended up using a similar function in ASP .NET.

Just out of interest is there any way of doing this with SQL alone.

Thanks B