MS Access Query similar to crosstab query
I have table tblSales with next fields:
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_SoldAll_Red_
….._Cap_Sold are Yes/No type filed in table.
I need query that will give me next result. In example I gave you I put just several entries in table that actually have several year entries of data.
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_Sold All_Red_Cap_Sold
Shop1 01/02/2016 Yes Yes No Yes
Shop1 01/10/2016 Yes Yes Yes No
shop1 01/23/2016 No No No No
Shop1 02/11/2016 Yes No No Yes
Shop1 02/20/2016 Yes Yes Yes Yes
Shop1 03/04/2016 No No Yes Yes
Shop2 01/03/2016 Yes Yes Yes No
Shop2 01/14/2016 No Yes No Yes
Shop2 01/28/2016 Yes No No No
Shop2 02/20/2016 No Yes Yes Yes
Shop2 02/25/2016 Yes Yes No No
Shop2 03/05/2016 Yes No No No
I need result as this:
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_Sold All_Red_Cap_Sold
Shop1 January/2016 2 2 1 1
Shop1 February/2016 2 1 1 2
Shop1 March/2016 0 0 1 1
Shop2 January/2016 2 2 1 1
Shop2 February/2016 1 2 1 1
Shop2 March/2016 1 0 0 0
..........
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_SoldAll_Red_
….._Cap_Sold are Yes/No type filed in table.
I need query that will give me next result. In example I gave you I put just several entries in table that actually have several year entries of data.
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_Sold All_Red_Cap_Sold
Shop1 01/02/2016 Yes Yes No Yes
Shop1 01/10/2016 Yes Yes Yes No
shop1 01/23/2016 No No No No
Shop1 02/11/2016 Yes No No Yes
Shop1 02/20/2016 Yes Yes Yes Yes
Shop1 03/04/2016 No No Yes Yes
Shop2 01/03/2016 Yes Yes Yes No
Shop2 01/14/2016 No Yes No Yes
Shop2 01/28/2016 Yes No No No
Shop2 02/20/2016 No Yes Yes Yes
Shop2 02/25/2016 Yes Yes No No
Shop2 03/05/2016 Yes No No No
I need result as this:
Shop DateSold All_Yellow_Cap_Sold All_Black_Cap_Sold All_White_Cap_Sold All_Red_Cap_Sold
Shop1 January/2016 2 2 1 1
Shop1 February/2016 2 1 1 2
Shop1 March/2016 0 0 1 1
Shop2 January/2016 2 2 1 1
Shop2 February/2016 1 2 1 1
Shop2 March/2016 1 0 0 0
..........
ASKER
Pat I need just mmm/yyyy grouping not Shops any more, I adjusted your solution it gives me column totals as first row but not proper numbers for mmm/yyyy rows just 1 or 0 in those rows. Should I use count instead sum??
ASKER
I your suggestion I changed Sum(IIf(All_Yellow_Cap_Sol
Sum(IIf(All_Yellow_Cap_Sol
Sum(IIf(All_Yellow_Cap_Sol
ASKER
In table those fields are seen as check boxes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Pat excellent as always!!
From YourTable
Group By Shop, Format(DateSold,"mmm/yyyy"