CorssTab using SQL

[prabhus]

I have a table with Orders Confirmed with Products A to C for which the orders are delivered or not.

I want to see the output with Confirmed orders which were not delivered to customer for each product category;
I dont want to do the crosstab query for each procuts seperatly so used sql but i have the issue in get the required output please help.

i used
SELECT Query1.[CONFIRMED], Count(Query1.Product_A) AS CountOfProduct_A, Count(Query1.Product_B) AS CountOfProduct_B, Count(Query1.[Product_C]) AS [CountOfProduct_C]
FROM Query1
WHERE (((Query1.Product_A)=False) AND ((Query1.Product_B)=False) AND ((Query1.[Product_C])=False))
GROUP BY Query1.[CONFIRMED];

I also attached Query1 result in excel and my requied out put in the Zip file.
please help.

 

[plog]

Sounds like your table isn't properly structured which prevents you from using a Cross-Tab query. Whatever datasource you want to use for the cross-tab shouldn't have a field for each Product, but instead a new row. Your data should look like this prior to going into the cross-tab:

Date, Confirmed, Product_Type, Product_Value
4/1/2014, TRUE, A, TRUE
4/2/2014, FALSE, A, TRUE
4/2/2014, TRUE, B, FALSE

That 3rd column is the key. You shouldn't have the product type as a field, but as a value. The above data you can cross-tab into the final result you want, your data you cannot.

Additionally, 'Date' is a poor choice for a field name because its a reserved word and will cause headaches when creating queries and code. I suggest your rename it by prefixing it with what the date represents (e.g. Product_Date, Confirmation_Date, etc.)