Question about duplicates in query

[BrianG]

I have a query that combines an Institution Table and a Persons Table.
The Institution may have 0 to 3 people associated with it. The
problem is when I run the query if an Institution has 2 or more people
associated with it, it shows up 2 or more times. Is there a way that
you can simply add aditional rows to the query and have the duplicate
people added to those rows. This query is going to be used to make a
dirsctorey in a mail merge document...

Go from this:

Institution1 Tom
Institution1 Frank
Institution1 Joe
Institution2 Mary
Institution2 George

To this:

Institution1 Tom Frank Joe
Institution2 Mary George

Many Thanks!!

 

[Duane Hookom]

You can use this generic concatenate function. You need to copy this into a
new module and save the module as modConcat. You can then use this function
in a query or even as the control source of a text box.

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

 

[BrianG]

Duane,

Thanks for the reply. I was off for a while and have just gotten back
to this problem. Anyway, I did as you instructed and saved the code
as a module and from there I am clueless. Is it correct to use this
module in the Field of the query. If so, I receive an error 'Command
text was not set for the command object.' When I select debug, places
the error here:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Thanks again...

You can use this generic concatenate function. You need to copy this into a
new module and save the module as modConcat. You can then use this function
in a query or even as the control source of a text box.

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 have a query that combines an Institution Table and a Persons Table.
The Institution may have 0 to 3 people associated with it. The
problem is when I run the query if an Institution has 2 or more people
associated with it, it shows up 2 or more times. Is there a way that
you can simply add aditional rows to the query and have the duplicate
people added to those rows. This query is going to be used to make a
dirsctorey in a mail merge document...

Go from this:

Institution1 Tom
Institution1 Frank
Institution1 Joe
Institution2 Mary
Institution2 George

To this:

Institution1 Tom Frank Joe
Institution2 Mary George

Many Thanks!!