Crosstab or Concatenate multple text selections for one value

[Guest]

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...

 

[Allen Browne]

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

 

[Guest]

Allen, any help if I give youthe field names and table names?

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.

Reply to group, rather than allenbrowne at mvps dot org.

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...

 

[Allen Browne]

You can work that out, Chris.

Just create a query that looks right, and then switch it to SQL View (View
menu.) There's an example of what you need.

You will learn much more if you work through that than if someone does it
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, any help if I give youthe field names and table names?

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

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...

 

[Guest]

Allen,

DId so, but get all sorts of erros, DOA errors, rs errors,...sorry thank
you for you help

You can work that out, Chris.

Just create a query that looks right, and then switch it to SQL View (View
menu.) There's an example of what you need.

You will learn much more if you work through that than if someone does it
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, any help if I give youthe field names and table names?

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


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...

 

[Guest]

Chris,

Make sure you have a reference set to the "DAO 3.6 Object Library", since
the code that Allen provided is DAO code. This reference is not set by
default in new databases created with Access 2000 or 2002. It is set by
default with new database created with Access 2003.

To set this reference, click on Tools > References when in the Visual Basic
Editor. Scroll down the list until you find this reference. In rare cases you
may need to browse for the file, in which case you want to browse for the
DAO360.dll file. After setting this reference and clicking on OK to dismiss
the references dialog, verify that you have Option Explicit as the second
line of code in your module. Here's why you want to see this:
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Then click on Debug > Compile ProjectName. Correct any compile time errors
that you might have.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Allen,

DId so, but get all sorts of erros, DOA errors, rs errors,...sorry thank
you for you help

 

[Guest]

Correction: It's called the "Microsoft DAO 3.6 Object Library".


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________