I am working in Access 2007 and have about 50 main products that I track unit sales on a monthly basis along with 150 accessories. Each product has 5 accessories maximum that can go with it, creating a one-to-many relationship. The accessories though, can go with more than one main product.
Although I am tracking sales, I need to create a report that will allow me to view the monthly sales for each accessory, as they relate to the main product sales. Basically, I am trying to get a better understanding of the ratio of main product sales to accessory product sales for each month. As a result, I hope to plan my purchases and inventory more efficiently.
I was trying to do this through the multi-valued field select row method by having 4 tables:
Then through a Prod_Relation TBL that links the Prod_Accessories TBL and the Prod_Products TBL, I have a multi-valued field that pulls in main product on each row in one field, and the multi-valued field contains all the accessories linked to the main product.
The part I am stumped on, is now how do I link the sales tables to set the report up that would allow me to capture the quantity sold for both main product and accessories?