STILL NEED HELP COUNTING CONSECUTIVE RECORDS>=1

[Guest]

THANKS TO MICHEL WALSH & JOHN VINSON i think i'm a step closer.
but i still need help trying to count consecutive records >=1, restarting
count at o when there is a record <1.

i renamed my "bal" field to "field"
to match the sql. provided.
i created q1 (query) by copying an pasting the first SQL:

SELECT a.date, a.BAL,
COUNT(*) AS Rank
FROM myTable AS a LEFT JOIN myTable AS b ON a.date<=b.date
GROUP BY a.date, a.BAL;

when i run the query i get the following:

date BAL Rank
6/15/2006 6 16
6/16/2006 9 15
6/17/2006 12 14
6/18/2006 -5 13
6/19/2006 0 12
6/20/2006 2 11
6/21/2006 5 10
6/22/2006 0 9
6/23/2006 8 8
6/24/2006 1 7
6/25/2006 4 6
6/26/2006 0 5
6/27/2006 25 4
6/28/2006 0 3
6/29/2006 0 2
6/30/2006 1 1

i'm not sure what to do next. i tried to copy an past the 2nd sql,

SELECT a.date, a.field, a.rank - Nz( Max(b.rank))
FROM q1 as a LEFT JOIN (SELECT rank FROM q1 WHERE field <1) As b
ON a.rank >= b.rank

into a second query, q2.

when i run q2 i get an error msg:

cannot find the input table or query 'SELECT rank FROM q1 WHERE field <1'.
Make sure it exists and that its name is spelled correctly.

i would appriciate a helping hand.

thanks

 

[Michel Walsh]

HI,



See the initial thread... is there still a problem?


Vanderghast, Access MVP

 

[Guest]

i added another post to the initial therad: "how do i count consecutive with
a field.....", dated 7/26/06. please check.