Multiple records for one name.

[Keith]

I'm trying to produce a report that lists a particular
field which has multiple records for the same person in
one line in a report.

At the moment the report lists the person every time in
the report for every time the field was used for that
person.

What I want to do is list the person once but print the
contents of the field side by side against that name.

Any suggestions would be most appreciated.

Thanks.

Keith.

 

[Duane Hookom]

Could you type a sample of what you would like displayed in your report? I
am having trouble understanding your desired record structure and desired
layout.

 

[Keith]

Duane,

Here is what I'm trying to achieve:

From

FName LName Factor
Fred Smith 50
Fred Smith 60
Fred Smith 110
..
..
..

To

FName LName Factors
Fred Smith 50, 60, 110 ...
..
..
..

Keith.

 

[Duane Hookom]

You should be able to use a generic Concatenate function in your query.
Create a new module and copy the function below into it. Save the module as
"basConcatenate". You can then create a Totals Query that groups by FName,
LName, and the function. It might look like:
SELECT FName, LName,
Concatenate( "SELECT Factor FROM tblA WHERE FName=""" & [FName] & """
And LName =""" & [LName] & """") as Factors
FROM tblA;

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