Hi,

I've got this query to get the most frequent value in a field (this may be wrong somewhere)....

SELECT TOP 1 Main_DB.Complainant_Name, Count(*) AS CallCount, First(Main_DB.Address) AS FirstOfAddress, First(Main_DB.Complainant_Phone) AS FirstOfComplainant_Phone
FROM Main_DB
WHERE (((Main_DB.Date_Logged) Between Date() And Date()-Month(1)))
GROUP BY Main_DB.Complainant_Name
ORDER BY Count(*) DESC;

I'm using this query to populate a report. However, I also need to display another field with multiple records from the same table. I need to display each of the values in the field "Complaint" corresponding to "Complainant_Name" derived from the above query.

How do I go about doing this?

Cheers,

Tubbs

Tubbs,

Do you mean something like this?

http://www.access-programmers.co.uk/forums/showthread.php?t=89617

Wayne

Thanks Wayne,

I made a function out of it.Eg,

Public Function GetComplaints() As String

Dim db As DAo.Database
Dim rst As DAo.Recordset, rs As DAo.Recordset
Dim strResult As String, Comp_Name As String

Set db = CurrentDb

Set rs = db.OpenRecordset("qryMostFreq")
Comp_Name = rs.Fields("Complainant_Name").Value
rs.Close
Set rst = db.OpenRecordset("Main_DB")
strResult = vbCrLf

rst.MoveFirst
Do While Not rst.EOF
If rst.Fields("Complainant_Name").Value = Comp_Name Then
strResult = strResult & rst.Fields("Log_no").Value & " - " & _
Format(rst.Fields("Date_Logged").Value, "dd/mm/yyyy") & _
" - " & rst.Fields("Complaint").Value & vbCrLf & vbCrLf
End If
rst.MoveNext
Loop

rst.close

If Len(strResult) > 0 Then
GetComplaints = strResult
End If

Many Thanks,

Tubbs

Tubbs,

Glad to hear it.

For future reference, when you post code use:

(code)
Your code ...
(/code)

But change the parentheses to square brackets. It will preserve the
indentation.

Wayne