Chris, the function below is aircode to give you a guide as to how to loop
through the matching records and concatenate the values.
The idea is to put this into a standard module (Modules tab of Database
window). You can then use it in a query such as this:
SELECT Plan, GetIDs([Plan])
FROM Table1
GROUP BY Plan;
Function GetIDs(varPlan As Variant) As Variant
Dim rs As DAO.Recordset
Dim strSql As String
Dim strOut As String
Dim lngLen As Long
Const strcSep = ","
'Loop through all matching records, concatenating the ID values.
If Not IsNull(varPlan) Then
strSql = "SELECT ID FROM Table1 WHERE Plan = """ & varPlan & """;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
strOut = strOut & rs!ID & strcSep
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
'Return the string without trailing separator, or Null.
lngLen = Len(strOut) - Len(strcSep)
If lngLen > 0 Then
GetIDs = Left(strOut, lngLen)
Else
GetIDs = Null
End If
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Chris" <(E-Mail Removed)> wrote in message
news:3A9D9492-7DCD-444D-8627-(E-Mail Removed)...
>I have a table that is a reference for a plan name and it's id. Some plans
> have multiple ID's. What I need is for a query to produce the following
> out
> of this table format:
>
> Plan ID
> BCBS MA 21
> UPMC 12
> BCBS MA 25
>
> Query Would produce
>
> PLAN ID
> BCBS MA 21,25
> UPMC 12
>
> I do not know SQL, is this possible, I've herad people say concatenate
> etc... but I only have one table with this fields...
Similar Threads
