Help Creating Relationships

[CIWEB]

I am having major issues getting relationships to work properly(I am sure it is a user error). My data is comprised of several Excel files that contain a wide varriety of data. What I am working with right now looks like this

Data 1                                                                       Data 2
ID#    Type         Name   Month                                     ID#       Income          Category
123     Night      John        12                                         123        65k                    A
123     Day         John         12                                        123        65K                    A
127     Night       Mary        5                                          517        65K                    A        
517     Night        Frank       11                                       621        100K                  S
621     Day          Linda         6
621     Day          Mark          6                                      
621     Day          Mark           7


What I would like to be able to do is create a visual with this data showing the Categories for each ID by Month. I assumed making a new table with the data sets being appended containing only the Mail# would allow me to do so, however after that step I am at a loss at what to do next, as I still get an error when I try to use data from both data sets. 

Does anyone know what I am doing wrong or what I need to do next?

Thank you in advance for your help and advice!

[Greg_Deckler]

I'm twice divorced so I'm not the expert on making relationships work correctly, but you need a unique key in one of your tables. In other words, one of your tables needs to only have unique values. You can either concatenate to create this key or create a third table that only has unique keys.

[CIWEB]

haha. Okay so I have created a third table that only contains the IDs and I removed all duplicates and I was able to create a relationship between this table and all of the data sets, however I still can not put variables from Data 1 and Data 2 together. I thought the third table would have been able to act as a link between the data sets, is that not the case?

[Greg_Deckler]

No, that should be the case. I'd have to know more about your model and data though to understand what is going on.

[CIWEB]

Does this help to provide a clearer picture? Each of the files that are linked have a common TH_Mail# with Mail # Master, however I am unable to build a visual using Data 1 and Data 2 together. Did I set the relationships up wrong?


Thanks

[CIWEB]