combined text fields using a GROUP BY statement

[Guest]

I'm working on grouping individual records together by families, and I need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST statements
to combine the field when there are only two records, but that clearly will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Douglas J. Steele]

Unfortunately, it's not that simple.

Take a look at my November, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Neil Sunderland]

I'm working on grouping individual records together by families, and I need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST statements
to combine the field when there are only two records, but that clearly will
not work for the groups that have more than two records.

The quick and dirty way of doing this would be to create three
separate queries and UNION them together afterwards: the first query
would get the families with one member, the second two-member families
and a third to get those with more than two members.

 

[Guest]

Your article was both saddening and enlightening. I was hoping for a "press
the magic button answer."

I think I can follow you for concantinating the strings together, though I'm
a stranger to VB. Do you have an answer if I want to create a sum for a
numerical values of fields in the same situation?

Like say, instead of telling who the member of the household is, assigning
them a numerical value and then giving a sum for the whole household?

Unfortunately, it's not that simple.

Take a look at my November, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Guest]

Yeah that is the way I've been doing it. The problem is that I've been
trying to create a field that is a combination of more than two records.
Take a quick look at what Doug wrote and that is the heart of my problem.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

 

[Guest]

Well I dropped your module into my database and then I changed my query to
what's included below. However I'm getting the error "Undefined Function
'DConcatenate'." Is there someother step that I missed in getting Access to
recognize the new module I dropped in. Do I need to compile it or anything?
I'm obviously woefully ignorant of VB and Modules.

Current Query:

SELECT First([SD20_VoterHistory].[FirstName]) & " and " &
Last([SD20_VoterHistory].[FirstName]) AS [Family First],
First([SD20_VoterHistory].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
DConcatenate("Party", "SD20_VoterHistory") AS PartyMix

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([SD20_VoterHistory].[FirstName]) AS [Family First],
First([SD20_VoterHistory].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
[Party] As PartyMix

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([SD20_VoterHistory].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
DConcatenate("Party", "SD20_VoterHistory") AS PartyMix

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

Unfortunately, it's not that simple.

Take a look at my November, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Guest]

I really feel like this is on the right track but I can't quite get it
to work. I'm getting the "Undefined function 'Concatenate' in expression"
error, which appearently can mean a myriad of things.

Duane, I dropped your module into my database, and I've included the
module, and my query below. I am pretty much one big bowl of n00bSauce when
it comes to VB, so if I need to compile or something like that please remind
me of that. I also tried switching the module code from DAO to ADO and
vice-versa but to no effect.

Please take a look and remind me of what I'm doing wrong.


Module Example:
Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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


Query Example:
SELECT First([SD20_VoterHistory].[FirstName]) & " and " &
Last([SD20_VoterHistory].[FirstName]) AS [Family First],
First([SD20_VoterHistory].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM SD20_VoterHistory WHERE Matchfield_Fam =" &
Matchfield_Fam)

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([SD20_VoterHistory].[FirstName]) AS [Family First],
First([SD20_VoterHistory].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM SD20_VoterHistory WHERE Matchfield_Fam =" &
Matchfield_Fam)

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([SD20_VoterHistory].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM SD20_VoterHistory WHERE Matchfield_Fam =" &
Matchfield_Fam)

FROM [SD20_VoterHistory]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

--
Duane Hookom
MS Access MVP

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Guest]

Still can't quite get it. I discovered that it's a bad idea to have your
modules and functions have the same name. My remaining obstacle is that it
concatenates the entire domain and won't limit itself to the recordset. My
query and module code are below. Can you take one more look for me?

Query Info:

SELECT First([TestTable].[FirstName]) & " and " &
Last([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([TestTable].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);


Module Info:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

--
Duane Hookom
MS Access MVP

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Neil Sunderland]

Yeah that is the way I've been doing it. The problem is that I've been
trying to create a field that is a combination of more than two records.
Take a quick look at what Doug wrote and that is the heart of my problem.

Sorry, I didn't make to the end of your post!

Anyway, are you just limited to T, S and R, and is the order they
appear in the final query important?

If the order is not important (ie if you don't mind returning RRSTTT
instead of TRSRT) then you can get away with something like this:

SELECT
'The ' & Min(F.LName) & ' Family' AS [Family First],
' ' AS [Family Last],
LEFT("RRRRRRRRRR",
(SELECT COUNT(*) FROM

AS X
WHERE X.FamID = F.FamID AND IdvType = 'R')) &
LEFT("SSSSSSSSSS",
(SELECT COUNT(*) FROM

AS X
WHERE X.FamID = F.FamID AND IdvType = 'S')) &
LEFT("TTTTTTTTTT",
(SELECT COUNT(*) FROM

AS X
WHERE X.FamID = F.FamID AND IdvType = 'T')) AS [FamType],
[FamID] AS [Family ID]
FROM

AS F
GROUP BY [FamID]
HAVING COUNT(*) > 2

 

[Guest]

I'm getting a "Too Few Parameters. Expected 1" error when I run it.


Query:

SELECT Matchfield_Fam,
DConcatenate("Party", "TestTable","Matchfield_Fam =" &
[TestTable].[Matchfield_Fam]) AS PartyMix

FROM TestTable;

Module:
Function DConcatenate( _
Expr As String, _
Domain As String, _
Optional Criteria As String = vbNullString, _
Optional Separator As String = ", " _
) As String

' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: A generic "concatenation" routine.
' Concatenates particular values from a specified set of
records.
'
' Expr An expression that identifies the field
' whose value you want to return.
' It can be a string expression identifying
' a field in a table or query, or it can be an
' expression that performs a calculation on data
' in that field.
' In Expr, you can include the name of a field in
a table,
' a control on a form, a constant, or a function.
If Expr
' includes a function, it can be either built-in
or user-defined,
' but not another domain aggregate or SQL
aggregate function.
' Domain A string expression identifying the set of
records that
' constitutes the domain.
' It can be a table name or a query name.
' Criteria An optional string expression used to restrict
the range of data
' on which the DConcatenate function is performed.
' For example, Criteria is often equivalent to
the WHERE clause in
' an SQL expression, without the word WHERE. If
criteria is omitted,
' the DConcatenate function evaluates Expr
against the entire domain.
' Any field that is included in criteria must
also be a field in Domain
' otherwise the DConcatenate function returns a
Null.
' Separator An optional string expression used to indicate
what character
' is supposed to be used to separate the
concatenated values.
' If not supplied, ", " (a comma followed by a
blank field) is used.
'
' Returns: A string representing the concatenation of the relevant set
of Expr in Domain,
' separated by Separator.

On Error GoTo Err_DConcatenate

Dim rstCurr As DAO.Recordset
Dim strConcatenate As String
Dim strSQL As String

strSQL = "SELECT " & Expr & " AS TheValue FROM " & Domain
If Len(Criteria) > 0 Then
strSQL = strSQL & " WHERE " & Criteria
End If

Set rstCurr = CurrentDb().OpenRecordset(strSQL)
Do While rstCurr.EOF = False
strConcatenate = strConcatenate & rstCurr!TheValue & Separator
rstCurr.MoveNext
Loop

If Len(strConcatenate) > 0 Then
strConcatenate = Left$(strConcatenate, Len(strConcatenate) -
Len(Separator))
End If

End_DConcatenate:
On Error Resume Next
rstCurr.Close
Set rstCurr = Nothing
DConcatenate = strConcatenate
Exit Function

Err_DConcatenate:
strConcatenate = vbNullString
Err.Raise Err.Number, "DConcatenate", Err.Description
Resume End_DConcatenate

End Function

Unfortunately, it's not that simple.

Take a look at my November, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into one.
The field name is "IndividualType". Any help would be much appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName]) AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Duane Hookom]

What's your domain and how should this be further limited?

--
Duane Hookom
MS Access MVP

Still can't quite get it. I discovered that it's a bad idea to have your
modules and functions have the same name. My remaining obstacle is that
it
concatenates the entire domain and won't limit itself to the recordset.
My
query and module code are below. Can you take one more look for me?

Query Info:

SELECT First([TestTable].[FirstName]) & " and " &
Last([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([TestTable].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);


Module Info:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

--
Duane Hookom
MS Access MVP

"In need of assistance" <[email protected]>
wrote
in message news:[email protected]...

I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in
the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into
one.
The field name is "IndividualType". Any help would be much
appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName])
AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Guest]

I removed some of the trash to simplify. My domain needs to be only the
grouped recordset. When I run this, it gives me the [Party] value for every
[Matchfield_Fam] in every field. It's somehow not stopping at just the
people that are in the family.


SELECT
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

What's your domain and how should this be further limited?

--
Duane Hookom
MS Access MVP

Still can't quite get it. I discovered that it's a bad idea to have your
modules and functions have the same name. My remaining obstacle is that
it
concatenates the entire domain and won't limit itself to the recordset.
My
query and module code are below. Can you take one more look for me?

Query Info:

SELECT First([TestTable].[FirstName]) & " and " &
Last([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([TestTable].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);


Module Info:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

--
Duane Hookom
MS Access MVP

"In need of assistance" <[email protected]>
wrote
in message I'm working on grouping individual records together by families, and I
need
help creating a text field that is the combination of all records in
the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into
one.
The field name is "IndividualType". Any help would be much
appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " & Last(

.[FirstName])
AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);

 

[Duane Hookom]

I'm not sure why you have an apostrophe following TestTable inside the
function call.

You don't have a group by. If you want to eliminate duplicates, you have to
eliminate duplicates:

SELECT [Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix
FROM [TestTable]
GROUP BY matchField_Fam,
Concatenate("SELECT Party FROM TestTable WHERE Matchfield_Fam =" &
[Matchfield_Fam]);


--
Duane Hookom
MS Access MVP

I removed some of the trash to simplify. My domain needs to be only the
grouped recordset. When I run this, it gives me the [Party] value for
every
[Matchfield_Fam] in every field. It's somehow not stopping at just the
people that are in the family.


SELECT
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

What's your domain and how should this be further limited?

--
Duane Hookom
MS Access MVP

"In need of assistance" <[email protected]>
wrote
in message news:[email protected]...

Still can't quite get it. I discovered that it's a bad idea to have
your
modules and functions have the same name. My remaining obstacle is
that
it
concatenates the entire domain and won't limit itself to the recordset.
My
query and module code are below. Can you take one more look for me?

Query Info:

SELECT First([TestTable].[FirstName]) & " and " &
Last([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);

UNION SELECT First([TestTable].[FirstName]) AS [Family First],
First([TestTable].[LastName]) AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=1);

UNION SELECT
"The " & First([TestTable].[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
[Matchfield_Fam] AS [Family ID],
Concatenate("SELECT Party FROM TestTable' WHERE Matchfield_Fam =" &
[Matchfield_Fam]) AS PartyMix

FROM [TestTable]

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);


Module Info:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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










:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Depending on your needs, you might have to modify the code a little.

The function could be used to sum fields from "child" records.

--
Duane Hookom
MS Access MVP

"In need of assistance" <[email protected]>
wrote
in message I'm working on grouping individual records together by families, and
I
need
help creating a text field that is the combination of all records in
the
group when the group has more than two records.

My query is below, you can see that I'm using the FIRST and LAST
statements
to combine the field when there are only two records, but that
clearly
will
not work for the groups that have more than two records.

To be clear, I am trying to group merge multiple string fields into
one.
The field name is "IndividualType". Any help would be much
appreciated.


Data Example:
FamID Fname Lname IdvType
2454 Joan Smith T
2567 Jack Franks T
2454 Bill Smith S
2567 Sue Franks T
2567 Timmy Franks R

Translates Into:

2454 Joan and Bill Smith TS
2567 The Franks Family TTR


Current SQL Query:


SELECT First(

.[FirstName]) & " and " &
Last(

.[FirstName])
AS
[Family First],
First(

.[LastName]) AS [Family Last],
First(

.[IndividualType]) & Last(

.[IndividualType]) AS
[FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)=2);


UNION SELECT
"The " & First(

.[LastName]) & " Family" AS [Family First],
" " AS [Family Last],
%%%%%%%%%%%% AS [FamilyType],
[Matchfield_Fam] AS [Family ID]

FROM

GROUP BY [Matchfield_Fam]

HAVING(Count(*)>2);