8 Replies Latest reply: Jun 10, 2016 1:33 PM by Christopher Marlow

# Joins based on multiple columns?

Hi There,

I do have 2 columns in table1 as below:

Employee_ID,Meeting_x,Meeting_y

101,0,1

111,1,2

121,3.3

table2:

Meeting_Identifier,Meeting_Desc

0,Not Met

1,Met Once

2,Met Twice

3,Met Thrice

so I need two different charts on statistics of Meetingx and Meetingy lets say 2 ppl Not Met ,3 ppl Met Thrice !!

I'm able to do it with a join on these two tables (Meeting_x & Meeting_Identifier) but I need join to happen on multiple columns from table 1 which also includes Meeting_y.

Any help on this ?

• ###### Re: Joins based on multiple columns?

What is the expected output here that you would like to achieve?

• ###### Re: Joins based on multiple columns?

Yes exactly and the desired o/p would  be :

on X axis : Not Met, Met Once, Met thrice

on Y axis : Count of ppl.

I have done this doing the join on Meeting X and Meeting Identifier but how about Meeting Y?

• ###### Re: Joins based on multiple columns?

my guess is that de value for the fields meeting x and meeting y correspond with meeting identifier? No?

employee 121 went to meeting x three times an to meeting y three times?

• ###### Re: Joins based on multiple columns?

Yes exactly and the desired o/p would  be :

on X axis : Not Met, Met Once, Met thrice

on Y axis : Count of ppl.

I have done this doing the join on Meeting X and Meeting Identifier but how about Meeting Y?

• ###### Re: Joins based on multiple columns?

I think you need to make 'Meeting' a column, rather than a set of columns, so recut your data by loading as follows

new_table:

Employee_ID,

'X' AS Meeting,

Meeting_x AS Meeting_Identifier

resident table1;

Concatentate (new_table)

Employee_ID,

'Y' AS Meeting,

Meeting_y AS Meeting_Identifier

resident table1;

• ###### Re: Joins based on multiple columns?

Hi,

A neat way of achieving this is via a mapping load and then a cross table.

Refer to attachment and see if it achieves what you are after.

I have tried to comment the code to tell you my thought process.

Hope that helps!

• ###### Re: Joins based on multiple columns?

Didn't you forget the attachement?

• ###### Re: Joins based on multiple columns?

I always forget crosstable, being an old MS Access hacker, it means something different to me ...