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 ListPurchase dateFruit NameVegitable NameFruit NamePricePrice Effective DateVegitable NamePricePrice Effective DateBasket A5/03/2000BannaCarrotApple31/01/2000Carrot31/01/2000Basket B5/05/2001Apple Apple35/10/2001Carrot35/10/2001Basket C3/05/2002 CarrotApple34/05/2002Carrot34/05/2002 Banna41/01/2000 Banna45/10/2001 Banna44/05/2002 Thanks in advance

Effective way of joining 3 tables

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

Join

Accepted answers

Qiu

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

0915-michellekim.yxmd

All comments

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