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:
-Prod_Accessories TBL
-Prod_Products TBL
-Sales_Accessories TBL
-Sales_Products TBL

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?

Any help will be a lifesaver