Multiple records for one name.

  • Thread starter Thread starter Keith
  • Start date Start date
K

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