Bridge Table multiple dif naming conventions

[MAS13]

So I am new to BI.  Some access exp.    

Problem

I have multiple datasheets with dif naming conventions

sheet 1 - first_last_number

sheet 2 - First Last

sheet 3 - First Last (info) number

1 report that needs to pull from all 3 sheets.  

I have tried a bridge table but 1) None of the names are in alpha order and if they are they don't match up because of former employees still in the list (which has to be there for HR reasons).  

Can someone show me how a bridge table without unique IDs would be setup to accomplish what i need?   or am i going about this the hard way?

Thank you in advance

[v-yulgu-msft]

Hi @MAS13,

Is there a common field in all three tables, first_last_number, First Last and First Last (info) number? If not, there is no way to establish relationships between them via bridge table.

Regards,

Yuliana Gu

[MAS13]

Also I didn't mention no unique id. Also as of now we r in testing so all I'm given is the xl dumps. If this goes live we will be pulling api's.

[v-yulgu-msft]

Hi @MAS13,

Please provide some dummy data to better describe your scenario.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[MAS13 · Accepted solution]

I actually think I figured it out.  I have 5 tables all with dif naming convention for user.  In the query part of bi, I extracted what I needed from that field, made a new column where the data was passed..  Then made the bridge table with the field user name that has a list of all employees in the company.  Built relationships between my 5 tables with the parsed user name and the bridge table..  This seems to work.. I have not tested fully yet.   If this sounds correct to you give me a thumbs up, if not please explain your way of solving this..  

If you see that somehow down the line this would be an issue please let me know.. 

Thank you in advance.

[v-yulgu-msft]

Hi @MAS13,

From your posted image, it looks like you have established the relationships successfully. But without sample data, I was able to imagine your actual scenario. So, it's hard to give any comment about this solution.

You said "I have 5 tables all with dif naming convention for user." How they are different? I want to see some detailed values of the name column in each table. And I also need to know how to map them depend on some specific rules.

Regards,

Yuliana Gu

[MAS13]

When you ask that do you mean common field name? Or the data? I can make all 3 tables w a common field name but the data within that field will never match. They all do have a date field in common. We are pulling stats from multiple companies and all of them do the naming convention for the user name dif. Sorry its early here. I hope I understood the question lol