Forum Discussion

MAS13's avatar
MAS13
Helper I
8 years ago
Solved

Bridge Table multiple dif naming conventions

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

  • MAS13's avatar
    MAS13
    8 years ago

    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.

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      MAS13
      Helper I
      8 years ago
      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
    • MAS13's avatar
      MAS13
      Helper I
      8 years ago
      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.