comapring 1 field with 2 fields from 2 tables by splitting the re.

[Guest]

One table has three fields for name and address, in some records names are
spllit in to two record and 2nd record contains remaining name with starting
of address(eg. first column has has name like 'Gorge', the 2nd column
has'Bush, 22 livinkstone' , some names are fully entered in 1st colum itself.
I have another table which contains one field for name. In the second table
soem names are given as 'bush, clinton,ferry' I want to compare names in the
2nd tables with the tow columns in the first table . As per example all the
names in 2nd table 'bush,clinton,ferry' has to be compared individually with
1st table, if all the three names mathces with the two colums in the 1st
table. records in each cell in both tables has to be checked like bush with
2columns, clinton with 2 colums and ferry with 2 colums in the first table.
If all the three words are there in the first table it should talke it has a
match and show the details.
If it is not very clear, I will send a sampel database which will be more
clear.
Please give me your emailId.
Thanks

 

[Jeff Boyce]

Not sure I can fully visualize your data structure...

One approach would be to come to some common ground before comparing. You
can use a query to concatenate multiple fields/columns together. If you
create one query on all the relevant fields in one table, and a second query
on all the relevant fields in your second table, you could compare the
contents by comparing the two queries. I believe you can use the "find
duplicates" query wizard to help with this final step.

A word of caution ... Access is quite literal. Spelling mistakes,
differences in spelling and abbreviation, and differences in punctuation and
spaces will result in Access NOT finding a match.