Advanced CountIf Query

[therzakid]

Hello, I'm trying to determine the best method to perform a query that counts paired orders. I have a database with sales data, TVs and other components (dvd players, blueray disc players, and remotes) and want to know how often a tv is sold with another component. For instance, there should count 3 BDP players because there were 3 paired orders that sold a BDP with a TV. One was paired with a 22" and the other 2 were paired with the 40". One remote was paired with the same 40" tv that a BDP was, so the total 40" category is only 2, not 3. And the total 22" tv paired with one of the 3 column headings counted to only one. I've included the data below for reference.

I'd really like to be able to do this on a big scale and hope that someone would be kind enough to take the time to help. Thank you!



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

Sample of the Query with relevant info (top table)



Summary Table, I really need the totals for the different television sizes. But, only the televisions sold with one of the three header products. And, the totals for the column header items (dvd, bdp, remote) (bottom table)

[June7]

I was able to accomplish close to your desired results with:

In the query that produced the example data you posted, include the following calculated fields.
ItemCount: DCount("OrderNo","Table1","OrderNo='" & [OrderNo] & "'")
Type: IIf(IsNumeric([SubCat]),"TV",[SubCat])
SizeTV: DLookUp("SubCat","Table1","Category='TV' AND OrderNo='" & [OrderNo] & "'")

Then do a Crosstab query.
TRANSFORM Count(OrderNo) AS CountOfOrderNo
SELECT SizeTV
FROM Query1
WHERE ItemCount>1
GROUP BY SizeTV
PIVOT Type;

Note that any tv sizes that do not have data in the source query will not show a record in the Crosstab. To show other tv sizes would require a join in the source query to a master list of all possible tv sizes (or some other more creative solution).

[therzakid]

Works great. Thank you very much for your help!