troubleshooting Question

MS Access Query similar to crosstab query

Avatar of Taras
TarasFlag for Canada asked on
Microsoft Access
6 Comments1 Solution137 ViewsLast Modified:
I have table tblSales with next fields:
Shop      DateSold      All_Yellow_Cap_Sold      All_Black_Cap_Sold      All_White_Cap_SoldAll_Red_Cap_Sold

….._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 CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros