Ms Access query count first

Question

I have the following table:

tblOrder
----------------------------
OrderID       ==>Primary Key
CustomerID    ==>Number
OrderDate     ==>Date
Order         ==>Number
Price         ==>Currency

[Order] field contain a number (like 11, 15, 17) which is a code.

I am trying to find a way, and if it is possible of course, to count [Order] per [CustomerID] that:

==>  I) Placed only [Order]=15
==> ii) Made first [Order]=15 and then placed another order which IS NOT 15.

Example Table: 
OrderID  CustomerID  OrderDate  Order  Price
--------------------------------------------
   1         3        1/1/2018    15     100
   2         2        3/2/2018    15     300
   3         2        7/3/2018    11     400
   4         3        2/6/2018    15     200
   5         1        5/2/2018    17     300
   6         1       11/7/2018    15     600
   7         2        1/4/2018    11     200

OutPut Query:
CustomerID  OnlyOrder=15  FirstOrder=15
---------------------------------------
   1           Null            Null    
   2             1               3    
   3             2             Null    

Thank you in advance.

Answer 1

This is much easier using aggregation and having. The following gets the customers:

select customerId
from tblOrders
group by customerId
having min(iif([order] = 15, orderdate, null)) < max(iif([order] <> 15, orderdate, null)) or
       (min([order]) = 15 and max(order = 15));

To count them, use a subquery:

select count(*)
from (select customerId
      from tblOrders
      group by customerId
      having min(iif([order] = 15, orderdate, null)) < max(iif([order] <> 15, orderdate, null)) or
             (min([order]) = 15 and max([order]) = 15)
     ) as c;

Edit:

If you want the first order to be 15 --which is not how I interpret the description -- that is also easy:

select customerId
from tblOrders
group by customerId
having min(iif([order] = 15, orderdate, null)) = min(orderdate) or
       (min([order]) = 15 and max(order = 15));

You can use this to count the customer ids.

EDIT:

For the revised question, you want something like this:

select customerId,
       iif(min([order]) = 15 and max(order = 15), count(*), 0) as all_first_15,
       iif(min(iif([order] = 15, orderdate, null)) = min(orderdate) and min([order]) <> max([order]), count(*), 0) as first_order_15
from tblOrders
group by customerId
having min(iif([order] = 15, orderdate, null)) = min(orderdate) or
       (min([order]) = 15 and max(order = 15));

Answer 2

Yup, that's quite a set of rules, but certainly possible.

Let's start with a subquery:

Select all customers where the first order (the order with the lowest (minimal) OrderID) is 15:

SELECT t.CustomerID 
FROM tblOrder t 
WHERE t.Order = 15 
AND EXISTS (
    SELECT 1 
    FROM tblOrder i 
    WHERE t.CustomerID = i.CustomerID 
    HAVING Min(i.OrderID) = t.OrderID
)

Then, we can use that to count all orders for these users:

SELECT Count(OrderID)
FROM tblOrder c
WHERE EXISTS (
    SELECT 1
    FROM tblOrder t 
    WHERE t.Order = 15 
    AND EXISTS (
        SELECT 1 
        FROM tblOrder i 
        WHERE t.CustomerID = i.CustomerID 
        HAVING Min(i.OrderID) = t.OrderID
    )
    AND t.CustomerID = c.CustomerID
)
GROUP BY c.CustomerID

Or, if you want to split out counts by 15 and not 15:

SELECT DISTINCT o.CustomerID, (SELECT COUNT(s2.OrderID) FROM tblOrder s2 WHERE s2.CustomerID = o.CustomerID AND s2.Order = 15) As Only15Count, (SELECT COUNT(s1.OrderID) FROM tblOrder s1 WHERE s1.CustomerID = o.CustomerID AND EXISTS (SELECT 1 FROM tblOrder s3 WHERE s3.CustomerID = s1.CustomerID AND s3.Order <> 15)) As TotalCount
FROM tblOrders o
WHERE EXISTS (
    SELECT 1
    FROM tblOrder t 
    WHERE t.Order = 15 
    AND EXISTS (
        SELECT 1 
        FROM tblOrder i 
        WHERE t.CustomerID = i.CustomerID 
        HAVING Min(i.OrderID) = t.OrderID
    )
    AND t.CustomerID = o.CustomerID
)

Note that, in contrast to your expected output, nothing will get returned if the first order isn't 15.