Comparing Values in multiple Fields

[roomaleuk]

Hi

I am trying to tidy up a database that contains customer name and bank account details

Part of this activity requires that I identify which Bank Accounts may not be directly related to the name of the customer and to ***** them in the following manner

a. Match fully
b. Partial match
c. No match

Currently I have the following fields
Name comprising
1. Title
2. Forename
3. Surname
4. Bank Account Name

A sample record may be
1. Mr
2. John
3. Smith

In this instance, I would look to mark records such as the following as being match fully

• Mr John Smith
• Mr J Smith
• Mr Smith
• Mr Smith esq.

I would want to indicate the following as partial matches

• Mr James Smith
• Mr D Smith
• Mrs Smith
• Mrs D Smith
• Mr & Mrs Smith
• Mr & Mrs J Smith
• Mr Smith-Harris
• Mr Smithson

I would want the following to be indicated as No Match

• Mr Jones
• Smith Electrical
• Mr & Mrs Larry Davis

I'm not sure if this is possible - but would appreciate any pointers

Thanks and best wishes

Andy

 

[Micron]

You will have to create separate queries for each condition. Note that if your data really is entered this way (Mr & Mrs J Smith) I'd say it's incorrect. Also, I don't agree with your logic. To me, Mr. Smith is not a full match for Mr. J. Smith, but I guess that doesn't matter.
For each, in a query design grid and on criteria line, enter IN("Mr","Mrs","Ms") in Title field. This will eliminate Smith Electrical.
Then for partial match: in Surname field, enter Like "Smith"
Ful match according to your design: in Forename field, enter Like "J*" OR Is Null. (Note, enter all from Like to Null, I'm not suggesting you pick on or the other).
For no match try the unmatched query wizard in Access
In these examples, you will have to change the names or convert them to parameters since you likely have a list of names in your records.;