How to determine actual technical field using data values

[jex57]

Hi
I have two databases, with different table and field names.
DB 1 - has a table called NMZ and has a field called F_Name (first name)
DB 2 - has a table called DTLS and has a field call NME - (first name)

What I want to be able to do is compare the data in F_Name to the data in NME and see if the data is similar so that I can link F_NAME to NME based on a probability that the data contained is similar.

Does anyone have any idea how to do this?

There are more fields than just one, so looking at how to do it across multiple fields and mulitple databases.

Thank you

 

[JonXL]

What do you mean by 'similar'?

The only real way to find out how they will compare in a query is to put them in a query. This will give you the records from NMZ that have names in DTLS:

SELECT 
   [F_Name]
FROM
   [NMZ]
WHERE
   [F_Name] IN
       (
           SELECT
               [NME]
           FROM
               [DTLS]
       )

 

[jex57]

but by using a query I would need to match each and every name to determine if the fields were the same. Am trying to get a way whereby it is almost assuming that there are (for example) 100 matches identical out of a subset of 1000 records and therefore it can assume that the two fields contain the same type of data.

 

[JonXL]

but by using a query I would need to match each and every name to determine if the fields were the same. Am trying to get a way whereby it is almost assuming that there are (for example) 100 matches identical out of a subset of 1000 records and therefore it can assume that the two fields contain the same type of data.

Are you saying you don't know which field in one of your tables contains the first names?

And you're trying to figure out which?

 

[xenou]

I believe the OP is saying that first name is just one of 1000 fields he wants to test ... going on the assumption that if 100 of 1000 fields from two records (in two different databases) match then the records are linked.

Really to give an specific advice I think more knowledge about the data is required. There are no rules for generic matching of data from two datasets. The likelihood of finding matches depends on what is in the tables.

Also note that such a query (as described in post 1) is not generally a good thing to be doing with SQL - queries can't be written this way without a lot of very tedious manual labor and you might have to compare every row to every other row, leading to potentially a query that will take hours or days to run depending on the size of the tables. I think you would really want to narrow down the matching to something other than "all fields" in order to make this more feasible.