advanced product attachment query

[therzakid]

Hello,

I'm trying to evaluate the ability of sales in attaching products to orders e.g. if the order (OrderNo) has a computer purchased, i'd like to see if they were able to attach an accessory product like Software, ESP (extended service plan), or whatever else I may want to see. I've identified the computers vs accessories by using Category (can be either VAIO or Accy). Also, I have SubCat as a subcategory that will classify the computers as a particular model. The end game is to have a query (crosstab?) that depicts the one below.

http://i1198.photobucket.com/albums/...id/dataimg.png

Also, June7 gave me some code for something similar and did what I asked but I was unable to adapt it to this project.

Thanks a ton and I've attached the db to see what I have done and for your testing purposes.



Kind regards,
Craig

[June7]

Your image shows source and ouput for televisions and accessories. The output summarizes by TV size. The table in file is computers and accessories. You should show example output for this. But I might have something anyway.

Expressions for the SELECT query.
ItemCount: DCount("OrderNo","Table1","OrderNo='" & [OrderNo] & "'")
MainItem: DLookUp("SubCat","Table1","Category='VAIO' AND OrderNo='" & [OrderNo] & "'")
SubCategory: IIf([Category]="Accy",[Subcat],"PC")

Crosstab query:
TRANSFORM Count(Query1.Units) AS CountOfUnits
SELECT Query1.MainItem
FROM Query1
WHERE ((Not (Query1.MainItem) Is Null) AND ((Query1.ItemCount)>"1"))
GROUP BY Query1.MainItem
PIVOT Query1.SubCategory;

Be aware, this crosstab is slow.

Did a spot check of totals. Filtering the SELECT shows there should be 212 ESP items but the crosstab shows 211. Order 0101028DMTH has two accessory items but no computer. So this is good, it is working.