Match data with different contents

[jeroen4691]

Hi,
I have to match an ID number with the correct user. In one table I have the columns userID and full name including initials, in the other table I have columns first name and last name without the initials.

table users:

Voornaam	Achternaam
Els	Worp van der

Table ID:

salnr	naam	achternaam
3827	Worp, E.G.J.M. van der	Worp

the result from the query needs to give me a column with the first name, the salnr, and the naam field.

How do I achieve this ?


Thanks in advance,

Jeroen4691

[admessing]

You likely need to add a primary key to one table and the same value as a foreign key in the other table....then it is just a matter of a simple SQL query.

[jeroen4691]

If it weren't so many records I would agree, however the full table holds about 3000 records, and how will I know which foreign/primary key to give to all those records?
I would think to create something that joins "achternaam" and "naam" in such a way that i achieve a 100% match between these two fields..

[orange]

Before you can relate these tables, you need to get some level of consistency (level of detail/consistent granularity) in the fields you intend to relate.
What does naam Worp, E.G.J.M. van der mean? Do all the naams have the same punctuation? Same field order? What if there are no initials?

The best you will get is an approximation. It appears that you will have to manually determine if

-E Worp = Els Worp van der

[jeroen4691]

All fields have the same punctuation, like lastname "comma" initials. the fiel I need to match is only "lastname" , so i need to find a filter to give me all lastnames before the "comma" .....

[recyan]

Hi,

table users:

Voornaam	Achternaam
Els	Worp van der

Table ID:

salnr	naam	achternaam
3827	Worp, E.G.J.M. van der	Worp

All fields have the same punctuation, like lastname "comma" initials. the fiel I need to match is only "lastname" , so i need to find a filter to give me all lastnames before the "comma" .....

Just trying to understand :

If it is simply getting "Worp" from the naam field "Worp, E.G.J.M. van der" from Table ID, given the criteria that the SurName "Worp" in the naam field is essentially and immediately followed by a comma, then :

ExtractedLastName: Left([naam],InStr([naam],",")-1)

which in turn can be used to join to the Achternaam from the Table Users.
However, I notice that the table Users has Achternaam as "Worp van der".

Also, is there no possibility of 2 Users having the same SurName.

Thanks