Relating different tables

[Oros]

Hello,

Would anybody know how to build this results table below using the product discount, customer and sales tables?  How do you relate these 3 tables and what formula/statement to use?  Thanks.

RESULTS TABLE

Customer#	Discount Level	Product #	Special Price
Cust 1	Bronze	B1	$30
Cust 1	Bronze	B3	$5
Cust 2	Gold	B2	$100
Cust 2	Gold	B1	$10
Cust 3	Silver	B3	$3

PRODUCT DISCOUNT TABLE

Product #	Discount Level	Special Price
B1	Bronze	$30
B1	Silver	$20
B1	Gold	$10
B2	Bronze	$150
B2	Silver	$125
B2	Gold	$100
B3	Bronze	$5
B3	Silver	$3
B3	Gold	$1

CUSTOMER TABLE

Customer	Discount Type
Cust 1	Bronze
Cust 2	Gold
Cust 3	Silver

SALES TABLE

Customer	Product #
Cust 1	B1
Cust 1	B3
Cust 2	B1
Cust 2	B2
Cust 3	B3

[Ashish_Mathur]

Hi,

Merge Table 2 into Table 3 based on the Customer column to bring over the Discount type column into Table 3.  Merge Table 3 into Table 1 based on the Product # and Discount type column to bring over the Customer in Table 1.  Now build all your visuals from Table 1.

Hope this helps.

[Oros]

Hi @Ashish_Mathur ,

Thank you for your quick reply.  In merging table 3 into table 1, what do you mean by based on Product # AND Discount Type column?  How do you achieve this merging using in a query, based on 2 columns? Thanks again.

[Ashish_Mathur]

Hi,

In the Query Editor, select Merge queries and then select the common columns of both tables.

[Oros · Accepted solution]

Thanks @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.