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" <(E-Mail Removed)> wrote in message
news:0e7801c37e1f$a7a0b900$(E-Mail Removed)
> 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

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)