>See some changes inside - however, I need again the input tables with BillCodes - something is not entirely right now in this schema.
Model table
---------------
Modelid Name
100 180 BR
110 225 SCR
Option Table
-----------------
optionid name ispackage
----------- -------------------------- ---------------
100 Deluxe Stereo Package 1
110 Sony 6 Disc Changer 0
120 100 Watt subwoofer 0
130 Swim transom remote 0
Modeloption table
-------------------------
modelid optionid packageid billcode
100 100 0 DLXSTREO180BR
100 110 100 SNYSTERO
100 130 100 SWMTANSREMO
110 100 0 DLXSTREO225SCR
110 110 100 SNYSTERO
110 120 100 SUBWOOF
110 130 100 SWMTANSREMO
You will notice that the packages can be different for different models. the 180BR doesn't have a subwoofer in this example.
If I was doing this in VFP for the entire project it wouldn't be so hard to break it up... but this dataset needs to be realtime queriable from Excel. I'm looking into doing a stored procedure and brute force the data into shape with multiple queries/processing if I have to.
Thanks again for all your help
>
>>For reference... this is the query I'm working with right now...
>>
>>
>>
>
>SELECT model.name as modelname, pnm.optionid as Packageoptionid, pnm.Name AS PackageName, ;
>onm.Name AS OptionName, onm.optionid as Optionid, mo.billcode as OptionBillCode ;
> FROM modeloption mo ;
> JOIN Option AS pnm ON pnm.optionid = mo.Packageid ;
> JOIN Option AS onm ON onm.optionid = mo.Optionid ;
> JOIN model ON model.modelid = mo.modelid ;
> WHERE model.name LIKE "%-10%";
> ORDER by model.sortorder, pnm.optioncatid, pnm.sortorder, onm.sortorder
>
>
>>
>>The where just restricts to model year 2010. Then I'm sorting by model, category, plus custom sort orders. added a join to show model name instead of just the modelid.
>>
>>Greg
