Grouping SQL results at 2 sub levels

Question

I am using an Access front end screen and SQL queries at the back end. I am trying to get a result set that is grouped at 2 levels: Region & Month and then a relevant count of total clients active in that period, clients with orders and clients without orders all within the period selected by the user.

Below are the 2 tables in use

Client Table A

    ID   |  Region   |  StartDate  |   Name
     1   |  North    |   1 Jan 16  |   ABC
     2   |  North    |   1 Mar 16  |   DEF
     3   |  East     |   1 Jul 16  |   GHE
     4   |  East     |   1 Aug 16  |   HIJ
     5   |  West     |   1 Feb 16  |   KLM
     6   |  West     |   1 Mar 16  |   NOP
     7   |  South    |   1 Apr 16  |   QUR
     8   |  South    |   1 Jan 16  |   STU

Orders Table B

    OrderID | Client ID   |Order Date
     1      |    1        | 15 Mar 16  
     2      |    3        | 15 Jul 16  
     3      |    5        | 15 Jun 16  
     4      |    8        | 15 Jul 16  
     5      |    6        | 15 Jul 16  
     6      |    4        | 15 Jan 16  
     7      |    2        | 15 Feb 16  
     8      |    1        | 15 Jul 16  
     9      |    3        | 05 Aug 16  
    10      |    3        | 16 Jul 16  
    11      |    2        | 15 May 16  
    12      |    4        | 15 May 16  
    13      |    6        | 15 May 16  
    14      |    7        | 15 Mar 16  

The User picks a start date and end date for the report for Eg 1 May 2016 to 31 Jul 16 I need a query that will evaluate if client start date is within reporting period and produce the following out put:

Result Set

    Region | Month  |Total Clients|Clients with Orders|Clients w/o Orders
    North  |May-2016|   2         |   1               |     1
    North  |Jun-2016|   2         |   0               |     2
    North  |Jul-2016|   2         |   1               |     1
    East   |May-2016|   0         |   0               |     0
    East   |Jun-2016|   0         |   0               |     0
    East   |Jul-2016|   1         |   1               |     0
    West   |May-2016|   2         |   1               |     1
    West   |Jun-2016|   2         |   0               |     2
    West   |Jul-2016|   2         |   1               |     1
    South  |May-2016|   2         |   0               |     2
    South  |Jun-2016|   2         |   0               |     2
    South  |Jul-2016|   2         |   1               |     1

Ive been stuck on this for 2 weeks now.. Please Help!!!

if it helps this is how far I have gotten

    PARAMETERS startDt DateTime, endDt DateTime, loc Text ( 255 );
    SELECT DISTINCT a.[Region] AS Region, 
    Format(b.[Orderdate],"MMM-YY") AS MonthOrder, 
    (Select Count(CMet.[clientID]) as cnt  from 
    (SELECT distinct b.[orderID], Format(b.[order date],"MMM-YY") 
    as Contact_Month, a.[clientID], a.[Region]
    FROM Client as a 
    INNER JOIN orders AS b ON a.[client ID] = b.[client id]
    WHERE iif(isnull(loc),a.[REgion] like '*',instr (loc,a.[region]))
    and (b.[orderdate] between startDt and endDt)  
    and (a.[Start date] < startDt) 
    GROUP BY a.[Region], Format(b.[order date],"MMM-YY"), 
    a.[clientID], a.[Region]
    HAVING count(a.[clientID]) >=1) as CMet) AS Clients_Met, 
    (select count([client id]) from clients where 
    iif(isnull(loc),[region] like '*',instr (loc,[region])) and 
    client.[Start date] < startDt AS Total_Client, 
    (Total_Client-Clients_Met) AS Not_Met, 
    format(Clients_Met/iif(Total_Client =0,1,Total_Client),'##.##%') AS Met_Percentage
    FROM clients AS a 
    INNER JOIN orders AS b ON a.[client ID]=b.[client id]
    WHERE iif(isnull(loc),a.[region] like '*',instr (loc,a.[region]))
    and (a.[Start date] < startDt 
    GROUP BY a.[region], Format(b.[order date],"MMM-YY")
    HAVING count(a.[client id]) >=1
    ORDER BY a.[region];

Answer 1

I tried the solution below. The idea is to do the grouping in the inner query, then do the summing in the outer one:

SELECT Region, Month, Count(ID) AS Clients, Sum(HasOrder) AS ClientsWithOrders, 
    [Clients]-[ClientsWithOrders] AS ClientsWithoutOrders
FROM (SELECT Region, CDate(Month([Order Date]) & "/1/" & Year([Order Date])) AS [Month], 
      ID, Max(IIf([Client ID]=[ID],1,0)) AS HasOrder
      FROM Orders, Client
      WHERE ((([Order Date])>=#5/1/2016# And 
          ([Order Date])<DateAdd("d",1,#7/31/2016#)))
      GROUP BY Region, CDate(Month([Order Date]) & "/1/" & Year([Order Date])), ID) 
         AS RegionMonthClient
GROUP BY Region, Month

The difficulty is I'm getting different results from you. For example, for East clients (3,4) I see one with an order in May (client 4) and one with an order in July (client 3).

Is this approach useful to you in any way?

Answer 2

You can use the join statement and to check the date use the BETWEEN operation. Here is the link for sample join statement and Between Opration.