count records

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

i have a report similiar to the data below
Bank No. records
abc mtg 2
abc mortgage 10
abr mortgage 10
abr mortg. 15

i have a few hundred lenders which are the same but the
spelling is different and i want to count the number of
records for each is this possible

abc mtg/mortgage = 12
abr mortgage/mortg. = 25
 
Dan said:
i have a report similiar to the data below
Bank No. records
abc mtg 2
abc mortgage 10
abr mortgage 10
abr mortg. 15

i have a few hundred lenders which are the same but the
spelling is different and i want to count the number of
records for each is this possible

abc mtg/mortgage = 12
abr mortgage/mortg. = 25
Click to expand...

It seems to me that you need to identify the specific words you want to
consider as equivalent. You could then have a function that returns an
adjusted lender name, like this:

Function fncAdjustedLenderName(LenderName As Variant) As Variant

Dim strAdjustedName As String

If IsNull(LenderName) Then Exit Function

strAdjustedName = Replace(LenderName, "mortgage", "mtg")
strAdjustedName = Replace(strAdjustedName , "mortg.", "mtg")
strAdjustedName = Replace(strAdjustedName , "mortg", "mtg")
strAdjustedName = Replace(strAdjustedName , "mtg.", "mtg")

fncAdjustedLenderName = strAdjustedName

End Function

Then you would use this function in a totals query like this:

SELECT
fncAdjustedLenderName(LenderName) As Lender
Count(*) As RecordCount
FROM BankData
GROUP BY fncAdjustedLenderName(LenderName);

All this is off the top of my head, but you get the idea, I hope.
 
Back
Top