how do I crossmatch two tables to find similar entries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table from the Bank Of England giving me the names of all the people
who are not allowed to hold a bank account. I then have our customer list. I
have put them into to tables in access. I do I crossmatch between the two
tables to find identical names and bring them to a new table.
 
What is the structure of your customer list and the table from the bank?

If they are both something like LastName, FirstName, and MI in both tables,
you would just link those two tables together by those fields. If different,
you may need to concantenate some of the fields together to get a match.

However I bet that you will be dissatified either way UNLESS both parties
had iron clad rules on inputting names. Also there's the problem of people
changing their names especially women who get married or divorced.

Depending on the number of records, this is often a job for good, old human
eyes to do.

Is there something besides the names, such as an Account Number that would
be a better match between the two tables?
 
First let me apologise as I was ment to give yo a good rating for getting
back to me. Yeh I was going through linking the tables but the bank of
England have to kept a very good standard format and the fields are all over
the place. Is it possible to set up a search function from each field to
telst another table.?

E.g using the name smith on our well ordered table and access searching the
whole of the other table for the name smith? And upon finding it processing
an make table query?

Kind Regards


David
 
Sure. Open a query in design mode. Bring in both the Bank table and your
table. Next drag and drop a line between the last name fields in both tables.

Of course this will connect all the Smiths in a semi-Cartisian join. For
example:

Bank
Smith John
Smith Jim
Smith Jack

Your Table
Smith John
Smith Jane
Smith Julie

Will produce the following permutations if only joined on the Last Name:

Smith Jack Jane
Smith Jack John
Smith Jack Julie
Smith Jim Jane
Smith Jim John
Smith Jim Julie
Smith John Jane
Smith John John
Smith John Julie
 
Back
Top