RUNNINGCOUNT-SO-FAR function ??

[milton747]

Hi,
I wish there was a RUNNINGCOUNT-SO-FAR function.
I have got the 1st 3 cols including ROWSPERCOUNTRY. (See SQL below) I've played with various aggregates, but can't get the WANT_UNIQUE_WITHIN_COUNTRY column.
Essentially a unique number for each customer within its country. (Restarts for each country)

Suggestions very welcomed
Milton


COUNTRY | CUNAME | ROWSPERCNTY | WANT_UNIQUE_WITHIN_COUNTRY
Aus | CustA | 3 | 1
Aus | CustB | 3 | 2
Aus | CustC | 3 | 3
Bel | CustE | 1 | 1
Can | CustD | 2 | 1
Can | CustX | 2 | 2



SELECT DISTINCT
CUSTOMERS.COUNTRY,
CUSTOMERS.CUSTOMERNAME,
sqa.RTX

FROM CUSTOMERS

INNER JOIN
(SELECT
CUSTOMERS.COUNTRY,
COUNT (DISTINCT CUSTOMERS.CUSTOMERNAME ) AS RTX
FROM CUSTOMERS
GROUP BY CUSTOMERS.COUNTRY
) sqa
ON sqa.COUNTRY = CUSTOMERS.COUNTRY

order by
CUSTOMERS.COUNTRY,
CUSTOMERS.CUSTOMERNAME

 

[dhookom]

How about using a ranking subquery

Rank: (SELECT COUNT(*) FROM CUSTOMERS C WHERE C.Country = Customers.Country and C.CUNAME <=Customers.CUNAME)

Duane
Hook'D on Access
MS Access MVP