Hi All
I'm trying to connect two tables ( in reality there are more than 100k rows, this is just example):
1st table is a table with the budget for each product
2nd table is a table with the cost by the product (price may vary for the same product category as we don't specify the brand, if discount applies etc).
I created relationship between the tables based on Product name. Then, once query is created I would like to extract the data to Excel and create pivot table showing total spend per product in 2016 vs 2016 budget.
Unfortunately Access is multiplying budget x no of products in the report. So for "Pen_red" final budget in the output file is 850 instead of 50. I was trying to select "unique values" or modify the pivot using eg. average but it doesn't work.
Could you please help me with building proper query? Thank you
1st table:
Product Budget 2016 Pen_red 50 Pen_green 45
2nd table:
Business Area Product Cost 2016 Pens Pen_red 10 Pens Pen_green 5 Pens Pen_green 6 Pens Pen_red 17