Create Relationship between Two Tables to represent inventory (HELP!)

v-sihou-msft

Thank you for your help.

I need to create the notion of total inventory and represent what portion of the inventory is either sold or available.  The inventory file represents the total amount of inventory I can sell in each market for 2017 and 2018.  The Sales Report represents the number of sales I have to date.  

I am trying to create a relationship between the two files that will illustrate a matrix view that represents a "complete view" of inventory with a value of Sold or Available for each row of Invnetory.  In essence, I am looking to replace a row of available inventory, when a slot is sold from the sales report.

With regard to your specific questions the Start Date is the day the campaign starts.

For example, if you look at Row 2 of the Sales Report:

this row would replace Row 60 of the inventory report (since this slot is sold)

With regard to the "Early" or "Late" comment...

Anything that has a Start Date <= the 15th of the month is the "Early" slot for that month.  Anything with a "Start Date" of >15th is a "Late" slot for that month.

Each market only has one Early and one Late slot to sell in a given month.

The matrix view would look something like this in its final form:

From the table above, you can see that Sold rows stem directly from the Sales Report.  The Available rows are from the Inventory report.  This table shows "June's" inventory for both Markets.

Please let me know if you have any additional questions, and thank you for your insight!