Complex query, don't know where to start
I have a promotion table that contains items that are on sale for a certain promotion. A promotion item can be setup based on the following criteria:
Item#, Dept, Class, Supplier, Product_Line,Category_1,Category_2,Category_3.
A promotion item can be entered by filling in any of the criteria above. For instance, items can be included in a promotion by entering just the Item#, or can be included by just specifying the Dept,Supplier,Product_Line,Dept & Class, etc. and all items that match on those details will be included in the promotion.
I need to make a join by Item# to a sales table to generate a sales summary for a promotion.
In order to first get a list of all item#'s that are in a promotion I need to do a look up in the inventory table to determine the item# for items that are included but were not associated directly by item# but by their details such as Dept,Class, Supplier...
Examples of some promotion table entrie are:
Item#___Dept__Class____Supplier___ProdLine___PromoID
34532______________________________________650034
________H5_________________________________650034
________H7____123__________________________650034
________________________ABC________________650034
A simple example of an inventory table:
Item#_Dept_Class_Supplier___Category__Cost__Retail
9833__H5___123___ABC______Home____5.99___7.99
3994__H7___543___ABC______Patio_____3.99___6.99
3423__D5___234___DEF______Auto______7.99___10.00
So joining with the promotion table on the supplier field I get a list of items that belong to the supplier 'ABC':
9833__H5___123___ABC______Home_____5.99
3994__H7___543___ABC______Patio______3.99
The sales table has a weekly/monthly/yearly record for each item where it keeps track of units sold, retail amount, etc ... So joining those records against the sales table will give me the sales summary for each item.
My problem is generating my item list to use in the sales table join because of the way promotions are stored.
Any help would be appreciated.