I was hoping someone could help with my data connection query.
Every month I receive a very large dataset (over 1GB) which has details of products sold in that month (Product Code, Units and Cash) by each customer (Customer ID).
Some months, not all customers will order products.
Some months, not all products will be ordered.
I want my reports to show sales by month for all accounts, and for all products.
My thinking is that I need two Master spreadsheets:
1. All Accounts that have ever ordered - Customer ID, Customer Group, Customer Address - this will be updated each month to check if any new customers have ordered who have not ordered before.
2. All products that have ever been ordered - Product Code, Product Name, Product Group - again, this will be updated each month to check if any new products have been ordered that have not been ordered before.
I can link my monthly sales to one of the master spreadsheets but not both. Does anyone know how I can link to both to enable my one report to show sales for all possible accounts and products over time?
Many thanks for any help