Many-to-Many Relationship Problem

[ChrisR22]

Hello,

I have several databases with employees names in different arrangements (First Last, Last First, First Letter Last Name, etc) and I am attempting to unify them all under a single index (First Last) so that I can simply search that name across all databases.

What I have done is created a table that has the desired search name ("Employee Name"), as well as a name column from every single database with the name variation on the same line as it.

Name Table:

Employee Name	Database 1 Name	Database 2 Name	Database 3 Name	Database 4 Name
John Smith	Jsmith	Smith, John		Smith, John (12345)
Jane Doe		Doe, Jane	Jane Doe	Doe, Jane (54321)
Tom Brady	Tbrady		Thomas Brady
Mike Jordan	Mjordan	Jordan, Michael	Michael Jordan	Jordan, Michael (23232)

Database 1

Column 1	Name	Column 3	Column 4
x	Jsmith	x	x
x	Bbonds	x	x
x	Tbrady	x	x
x	Mjordan	x	x
x	Wgretzky	x	x

Database 2

Column 1	Column 2	Name	Column 4
x	x	Smith, John	x
x	x	Doe, Jane	x
x	x	James, LeBron	x
x	x	Jordan, Michael	x
x	x	Shakespeare, William	x
x	x	Pitt, Brad	x

As you can see, there are no duplicates in the Name Table for the columns related to database 1 and 2, there are however null values because those employees do not appear in database 1 or 2.

As you can see there are also a few unique values in databse 1 and 2 because those employees are not relevant and I do not wish to include them in my Name Table.

When I attempt to create relationships between each of these databases via their associated columns,

ex. Name > Database 1 Name

it only allows me to create many-to-many relationships. I am confused because there are unique values in the database columns (wgretzky; Pitt, Brad), but no unique values in that associated column in the Name Table; would that not constitute a Many-to-One relationship from Database to Name Table? Am I missing something, are the null spaces causing this issue?

On another note, if this is not the proper approach to create a unified naming system that can be utilized by a slicer to search common aliases across multiple tables, then please, I am open to other suggestions for methods.

[amitchandak]

@ChrisR22 , Because the join column has a blank value. Change it from many to many single direction from Table to database?

Or fill null values with Employee Name

[ChrisR22]

Hey @amitchandak , I'm not sure I understand your first instruction, or was that a question?

I am not able to fill those null values in the Name Table with values because in many cases there aren't values because the people are not present in the databases.

Do you have a workaround, or another method to establish a Common Alias Table that can be used in the manner in which I described previously?