Joins based on multiple columns?

[#1]

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 ?

Thanks in advance !!

[sunny_talwar]

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

[joris_lansdaal]

‌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?

[#4]

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?

[#5]

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?

[chrismarlow]

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:

Load

     Employee_ID,

     'X' AS Meeting,    

     Meeting_x AS Meeting_Identifier

resident table1;

Concatentate (new_table)

Load

     Employee_ID,

     'Y' AS Meeting,    

     Meeting_y AS Meeting_Identifier

resident table1;

Then drop your original table1.

[njmaehler]

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!

[joris_lansdaal]

Didn't you forget the attachement?

[chrismarlow]

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