I tried to do many things at one. First, the ranking technique using joins
works only if there is no dup.

So, better to take an example.

Using Northwind, as example, I want to get the sequence of orderDate, per
employee, in other words, find, per employee, how many consecutive days they
succeed in placing at least one order.

First, I built a query returning no dup:

-------------------
SELECT DISTINCT Orders.EmployeeID, Orders.OrderDate
FROM Orders;
--------------------

which I call DistinctEmployeeOrderDate.

Next, a query to do the ranking it self:

-----------------------
SELECT a.EmployeeID, a.OrderDate, COUNT(*) AS rank
FROM DistinctEmployeeOrderDate AS a
   INNER JOIN DistinctEmployeeOrderDate AS b
   ON (a.EmployeeID=b.EmployeeID) AND (a.OrderDate>=b.OrderDate)
GROUP BY a.EmployeeID, a.OrderDate
ORDER BY a.EmployeeID, a.OrderDate;
-------------------------

and finally, use the group on date-rank, to get the consecutive dates:

---------------------------------------
SELECT TOP 5
    x.EmployeeID,
    Min(x.OrderDate) AS MinOfOrderDate,
    Max(x.OrderDate) AS MaxOfOrderDate
FROM EmployeeDateRank AS x
GROUP BY x.EmployeeID, x.orderDate-x.rank
ORDER BY Max(x.orderDate)-Min(x.orderDate) DESC
---------------------------------------

returning

  Employee MinOfOrderDate MaxOfOrderDate
     Peacock, Margaret 1998.03.02 1998.03.06
     Fuller, Andrew 1998.04.06 1998.04.10
     Leverling, Janet 1997.02.10 1997.02.13
     Fuller, Andrew 1998.03.30 1998.04.02
     Callahan, Laura 1997.10.27 1997.10.30
     Buchanan, Steven 1998.02.03 1998.02.06

where we can see that Andrew succeed to place (at least) one order for 5
consecutive dates  (1998.04.06 to 1998.04.10)

If you tried to use directly the GROUP BY  a.date-COUNT(*), you will
definitively get the error that you cannot use an aggregate in the group
clause. You have to use a sub-query (or cascading the queries, as here).

Vanderghast, Access MVP