Hello mfoster,
1) In an existing module or in a new module paste following:
'===
Option Compare Database
Option Explicit
Function findDupNames(aName As String) As String
Dim lenName As Integer
Dim baseName As String
Dim Kounter As Integer
Dim baseChar As String
Dim charCode As Integer
lenName = Len(aName)
baseName = ""
For Kounter = 1 To lenName
baseChar = UCase(Mid(aName, Kounter, 1))
charCode = Asc(baseChar)
If ((charCode > 64) And (charCode < 91)) Then
baseName = baseName & baseChar
End If
Next
findDupNames = baseName
End Function
'==
2) Create a query 'qDupNames' based on data table.
3) Create a calculated field in query by typing 'dName: FindDupNames(cName)' in field name row. cName assumed to be name of fieldthat contains names.
4) Move the calculated field to make it first column in the query. (very imp.)
5) Make sure 'Total:' row is displayed in Query Design Grid. All fields should have value 'Group By' in 'Total:' row.
6) Create report 'rDupNames'based on 'qDupNames' using 'design View'. AutoReport / wizards will not work.
7) Include fields you want in report. DO NOT INCLUDE dNames.
8) In the design view of the report, click on View > Sortring & Grouping menu.
In 'Sorting & Grouping' dialog box select dName field in the first row and ascending/descending 'Sort Order'.
Set 'Group Header' and 'Group Footer' to 'Yes', 'Group On' to 'Each Value', 'Group Interval' to '1' and 'Keep Together' to 'Whole Group'.
This will add two new sections in report.
In 'dName Header' you can add group heading. In 'dName Footer' you may include group totals.
Each group will show duplicate records of a name. Single records will also be listed.
Save report and click 'Preview'. What you will see is what you needed.
hope this helps.
For more details/clarifications contact me.
with regards,
Sanjay Kulkarni,
'Simple And Effective Solutions to Small Problems, No QuickFixes.'
