Hi all
I'm trying to join below three tables on matching on different fields.
For example below...
Is there a easier way of joining the three tables to get the sum price per basket (in table 1)? I was using 'Join' but I didn't like the idea of using multiple joins..
Table 1 | Table 2 | Table 3 | |||||||
Grocery List | Purchase date | Fruit Name | Vegitable Name | Fruit Name | Price | Price Effective Date | Vegitable Name | Price | Price Effective Date |
Basket A | 5/03/2000 | Banna | Carrot | Apple | 3 | 1/01/2000 | Carrot | 3 | 1/01/2000 |
Basket B | 5/05/2001 | Apple | Apple | 3 | 5/10/2001 | Carrot | 3 | 5/10/2001 | |
Basket C | 3/05/2002 | Carrot | Apple | 3 | 4/05/2002 | Carrot | 3 | 4/05/2002 | |
Banna | 4 | 1/01/2000 | |||||||
Banna | 4 | 5/10/2001 | |||||||
Banna | 4 | 4/05/2002 | |||||||
Thanks in advance
Solved! Go to Solution.
multiple joins are the way if you are joining by different fields. the multiple join tool only works if the join fields are the same between all the tables. if you only have 3 tables, just do regular joins. if there's hundreds of joins you need to make for some reason, you could build a macro
@michellekim
I am sorry but I dont think can avoid using join here, if not multiple.
I use one join, and also notice there is trick here about the effective price date. Hope my understanding is correct.
Brilliant how did I not think about that 😄
Thanks Castor
Thank you for the tips
@michellekim
Glad I can help.
I am thinking to submite this top as an idea of weekly challenge and hope you are fine with it.
@Qiu Yup! I'm happy with you sharing 😄
@michellekim
Thank you for agreeing with it. 😁