list multiple results in one field

  • Thread starter Thread starter Mike Abrahamson
  • Start date Start date
M

Mike Abrahamson

I would like my query to display results so that each customer ID is listed
only once, while the codes assigned in a separate table are displayed as a
delimited list in one field. For instance, right now it shows up as this:

CustomerID Category Code
001 X A
001 X B
002 X A
003 X A
003 X B
003 X C

But I want it to display as this:

CustomerID Category Code
001 X A; B
002 X A
003 X A; B; C

Is there a simple expression I can use or does this require some major
coding? I'm at a loss and have searched high and low for the answer. Can
this be done?

Thanks a million for any help!
Mike Abrahamson
 
-----Original Message-----
I would like my query to display results so that each customer ID is listed
only once, while the codes assigned in a separate table are displayed as a
delimited list in one field. For instance, right now it shows up as this:

CustomerID Category Code
001 X A
001 X B
002 X A
003 X A
003 X B
003 X C

But I want it to display as this:

CustomerID Category Code
001 X A; B
002 X A
003 X A; B; C

Is there a simple expression I can use or does this require some major
coding? I'm at a loss and have searched high and low for the answer. Can
this be done?

Thanks a million for any help!
Mike Abrahamson


.
Have you tried making a cross tab query
Click to expand...
 
Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
According to my limited understanding of cross-tab queries, I'd end up with
the codes in one row, but each one would be in its own column. I need them
all listed in the same field for each CustomerID.

Mike
 
Thanks for the code. I'll give it a try this week.

Mike


Duane Hookom said:
Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


Mike Abrahamson said:
I would like my query to display results so that each customer ID is listed
only once, while the codes assigned in a separate table are displayed as a
delimited list in one field. For instance, right now it shows up as this:

CustomerID Category Code
001 X A
001 X B
002 X A
003 X A
003 X B
003 X C

But I want it to display as this:

CustomerID Category Code
001 X A; B
002 X A
003 X A; B; C

Is there a simple expression I can use or does this require some major
coding? I'm at a loss and have searched high and low for the answer. Can
this be done?

Thanks a million for any help!
Mike Abrahamson
Click to expand...
Click to expand...
 
Duane,

Could you give me some guidance? I created a query following your example
and filled in my own field and table info. Then I went into the expression
builder for the Concatenate function and entered the following code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

but when I leave the builder I get an error that there is invalid syntax in
the expression. It highlights the underscore at the end of line 2. Do I
need to replace that with something or remove it?

I'm new to using ADO code in a query so a little help would be greatly
appreciated.

Thanks for your help thus far,
Mike

Mike Abrahamson said:
Thanks for the code. I'll give it a try this week.

Mike


Duane Hookom said:
Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


Mike Abrahamson said:
I would like my query to display results so that each customer ID is listed
only once, while the codes assigned in a separate table are displayed
Click to expand...
Click to expand...
as
a
Click to expand...
Click to expand...
 
The concatenate function belongs in a module. You can then use the function
in a control source or a query like you would use any other function. Your
expression builder syntax would be something like:
=Concatenate("your sql statement")

--
Duane Hookom
MS Access MVP


Mike Abrahamson said:
Duane,

Could you give me some guidance? I created a query following your example
and filled in my own field and table info. Then I went into the expression
builder for the Concatenate function and entered the following code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

but when I leave the builder I get an error that there is invalid syntax in
the expression. It highlights the underscore at the end of line 2. Do I
need to replace that with something or remove it?

I'm new to using ADO code in a query so a little help would be greatly
appreciated.

Thanks for your help thus far,
Mike

Mike Abrahamson said:
Thanks for the code. I'll give it a try this week.

Mike


Duane Hookom said:
Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


I would like my query to display results so that each customer ID is
listed
only once, while the codes assigned in a separate table are
Click to expand...
Click to expand...
displayed
as answer.
Can
Click to expand...
Click to expand...
 
OK, I created a module called Concatenate and entered the code as-is. I
then went into my query and defined the expression as you recommended. Now
when I try to run the query I get an error "Undefined function "Concatenate"
in Expression." I am absolutely clueless and really appreciate your
patience with me. I understand if you can't help me any further, but is
there something simple I'm not doing out of sheer ignorance?

Mike

Duane Hookom said:
The concatenate function belongs in a module. You can then use the function
in a control source or a query like you would use any other function. Your
expression builder syntax would be something like:
=Concatenate("your sql statement")

--
Duane Hookom
MS Access MVP


Mike Abrahamson said:
Duane,

Could you give me some guidance? I created a query following your example
and filled in my own field and table info. Then I went into the expression
builder for the Concatenate function and entered the following code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "; ") _
As String
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

but when I leave the builder I get an error that there is invalid syntax in
the expression. It highlights the underscore at the end of line 2. Do I
need to replace that with something or remove it?

I'm new to using ADO code in a query so a little help would be greatly
appreciated.

Thanks for your help thus far,
Mike

Mike Abrahamson said:
Thanks for the code. I'll give it a try this week.

Mike


Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


I would like my query to display results so that each customer ID is
listed
only once, while the codes assigned in a separate table are
Click to expand...
displayed
as
a
delimited list in one field. For instance, right now it shows up as
this:

CustomerID Category Code
001 X A
001 X B
002 X A
003 X A
003 X B
003 X C

But I want it to display as this:

CustomerID Category Code
001 X A; B
002 X A
003 X A; B; C

Is there a simple expression I can use or does this require some major
coding? I'm at a loss and have searched high and low for the answer.
Can
this be done?

Thanks a million for any help!
Mike Abrahamson
Click to expand...
Click to expand...
Click to expand...
 
You can't name the module the same as the function. The module should be
renamed to something like "basConcat".

--
Duane Hookom
Microsoft Access MVP


Mike Abrahamson said:
OK, I created a module called Concatenate and entered the code as-is. I
then went into my query and defined the expression as you recommended. Now
when I try to run the query I get an error "Undefined function "Concatenate"
in Expression." I am absolutely clueless and really appreciate your
patience with me. I understand if you can't help me any further, but is
there something simple I'm not doing out of sheer ignorance?

Mike

Duane Hookom said:
The concatenate function belongs in a module. You can then use the function
in a control source or a query like you would use any other function. Your
expression builder syntax would be something like:
=Concatenate("your sql statement")

--
Duane Hookom
MS Access MVP


syntax
in
Click to expand...
Do
I
need to replace that with something or remove it?

I'm new to using ADO code in a query so a little help would be greatly
appreciated.

Thanks for your help thus far,
Mike

Thanks for the code. I'll give it a try this week.

Mike


Here is a generic function that I wrote to handle this type of request:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP


I would like my query to display results so that each customer
Click to expand...
Click to expand...
ID
is
Click to expand...
up
as
Click to expand...
Click to expand...
 
Hi Mike,

I still don't know if it can be done with pure SQL, but i got it working.
The trick is using two queries and a bit of VBA code to build up a recordset
with a recordkey and a populated textfield. Just join to this query through
the recordkey to get the populated textfields. It works faster than looping
through records in VBA.

1. The base query "qryYourBaseQueryForPopulating" GROUPS the Recordkey
GROUPS the Textfield you want to populate. In a 3th column a calculated
textfield as EXPRESSION calls a public function fnPopulateTextfield which
takes to params (Recordkey and Textfield).


RecID
tblYourSecundairyTable
GROUP BY

SomeTextfieldYouWantToPopulate
tblYourSecundairyTable
GROUP BY

PopulatedText: fnPopulateTextfield([RecID];[SomeTextfieldYouWantToPopulate])

EXPRESSION

The second column is needed because without it the query does not recognizes
that "SomeTextfieldYouWantToPopulate" is part of a statistic function! The
side effect is that it will group on every "SomeTextfieldYouWantToPopulate",
but the final query will correct this!

2. The finishing query "qryYourFinishingQueryForPopulating" based on the
above query GROUPS again on the Recordkey and takes the LAST record of the
calculated populated textfield.

RecID
qryYourBaseQueryForPopulating
GROUP BY

PopulatedText
qryYourBaseQueryForPopulating
LAST

3. The function fnPopulateTextfield uses two STATIC vars (long and string).
Every time it sees a different recordkey it reinitializes the STATIC vars
(Long var gets new recordkey and the String var gets empty). Next it adds
the Textfield to the STATIC string var seperated by a comma and then returns
the STATIC string var.

Lets say you have a secundairytable "tblYourSecundairyTable" like the next
one :

RecID SomeTextfieldYouWantToPopulate
1 Cat
1 Dog
2 Fish
3 Lion
3 Birth
3 Snake
4 Dog
4 Crock
4 Hyena
4 Fish

After the base query "qryYourBaseQueryForPopulating" you will get:

RecID PopulatedTextfield
1 Cat
1 Cat, Dog
2 Fish
3 Lion
3 Lion, Birth
3 Lion, Birth, Snake
4 Dog
4 Dog, Crock
4 Dog, Crock, Hyena
4 Dog, Crock, Hyena, Fish

What the f*#%! Don't worry after the final query
"qryYourFinishingQueryForPopulating" you will get:

1 Cat, Dog
2 Fish
3 Lion, Birth, Snake
4 Dog, Crock, Hyena, Fish

Voila! Now it's just a matter of joining to this query from your
primarytable.

My own code was too complex to show the basic principle, sorry!

Good luck and if you believe hard enough it will happen!

Sidney.
 
Back
Top