How to determine actual technical field using data values

jex57

Board Regular
Joined
Oct 29, 2015
Messages
53
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
306
Office Version
365, 2016
Platform
Windows
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:

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

jex57

Board Regular
Joined
Oct 29, 2015
Messages
53
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

Active Member
Joined
Feb 5, 2018
Messages
306
Office Version
365, 2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,603
Office Version
2013
Platform
Windows
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.
 
Last edited:

Forum statistics

Threads
1,089,638
Messages
5,409,474
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top