Effective way of joining 3 tables

[michellekim]

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

[mst3k]

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

[Qiu · Accepted solution]

@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.

[michellekim]

Brilliant how did I not think about that 😄 

Thanks Castor

[michellekim]

Thank you for the tips

[Qiu]

@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.

[michellekim]

@Qiu Yup! I'm happy with you sharing 😄  

[Qiu]

@michellekim 
Thank you for agreeing with it. 😁