how do i count consecutive records with a field value >=1

[Guest]

restarting count after a record with a field value <1. (count should be
based on query order OR form order of records, not the order of the records
in their table.) I DON'T KNOW VISUAL BASIC, SO IF IT'S POSSIBLE I WANT TO
USE an EXPRESSION IN A QUERY OR ON A FORM.
i.e.
field
date value count THANK YOU.
6/1/06 2 1
6/2/06 8 2
6/3/06 4 3
6/4/06 0 0
6/5/06 6 1
6/6/06 9 2
6/7/06 -23 0

 

[Michel Walsh]

Hi,



SELECT a.date, a.field, COUNT(*) as Rank
FROM myTable As a LEFT JOIN myTable As b
ON a.date <= b.date
GROUP BY a.date, a.field


will do a standard count, so, saved it, say, as q1, then



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



should adjust the count as specified.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

thank you, thank you, thank you for your reply. i'm a novice, so i'm sorry
to say that i didn't understand what you were telling me.
for this problem i created "mytable" with two fields: date, & bal (the 'bal'
is field i want to count consecutive records when the 'bal' field is >=1.
when a records is <1 the count should go to "0". the next record >=1 should
start the count over at '1'.
i'm sure you understand what i'm trying to do, but i repeated it just to
make sure.

if possible could you be more litteral -- i.e.
what goes in each column of q1 (query) - what field names & the criteria for
each column. and above the query grid -- anything other than one display of
"mytable"

q2 (query2) ????

i hope you have the time to reply because i'm lost.

thank you again

betty

 

[John Vinson]

if possible could you be more litteral -- i.e.
what goes in each column of q1 (query) - what field names & the criteria for
each column. and above the query grid -- anything other than one display of
"mytable"

Vanderghast's suggestion was NOT something that you enter in the query
grid. The grid is actually just a tool to build a SQL string, the
"real" query - and that's what Michel posted.

Open a new Query. Select View... SQL. Copy and paste his suggested
SQL (change any tablenames or fieldnames necessary to match your
table).

You can then go back to the query grid to see how Access would display
it there.

John W. Vinson[MVP]

 

[Guest]

THANKS. well i think i'm a step closer. i renamed my "bal" field to "field"
to match the sql.
i created q1 (query) by copying an pasting the first "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 5
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.

thanks

 

[John Vinson]

THANKS. well i think i'm a step closer. i renamed my "bal" field to "field"
to match the sql.

:-{(

That's not what I meant, bettyboop. I would suggest leaving your table
alone and changing the names in the QUERY.

i created q1 (query) by copying an pasting the first "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 5
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.

Sounds like you didn't save the first query using the name q1.

John W. Vinson[MVP]

 

[Guest]

Hi John
i tried using the orig table field names "date" and "bal".
i created a new query named "q1" and copied and pasted the 1st sql changing
the sql "field" to 'bal'.
when i run the query i get a reverse count in the 'rank' column.

i created a 2nd query named q2 and copied and pasted the 2nd sql changing
the sql "field" to "bal".

when i run q2 i get the error msg i stated.

i then tried the using the sql name as provided and changing the names in my
table so they would match. q1 gives the same result. when i run q2 i get
the same error msg.

is the reverse count the correct result from q1?????? did i at least take
one step in the right direction?? (i thought i did)

as to q2 (query) i don't know what's wrong. is it possible the sql has a
typo or other glich??? could you try it to see if it works for you?


i appreciate your help.

p.s. - eariler i didn't see my original, so i reposted. then later i i
found my post and your reply.. i hope i didn't confuse/complicate my request
for help.

thanks again

 

[Michel Walsh]

HI,

For the reverse count, it is a matter to reverse the inequation, try
a.date>= b.date instead of a.date <= b.date, in the ON clause, of the first
query, q1. It is a little bit like ordering in ASCending or DESCending
order... it is the same, but exactly the opposite


In query2, you should also replace rank and field, which are fields name, by
what you really use. I should have explained my convention to use ALL CASE
word as key word to keep as they are and other words are generally "case by
case" words to be replaced. In our case, I used, in query1:


SELECT a.date, a.field, ... AS rank FROM ...


and, in query2, I re-used the same "to be replace" expressions:

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



so, if you replace only "field" by "bal", and used rank as I did, in
query1, then your query2 should be:


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


Note that "field" was occurring twice, so now, "bal" occurs twice too, the
second time, in the: (SELECT ... ) as b




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel

thanks again for trying to help.

when i copy and past your SQL into q2 and run by selecting datasheet
view(before saving) i get an error msg,"you tried to execute a query that
does not include the specified expression 'date' as part of an aggregate
function."

if i save q2 and then run by selecting datasheet view i get a different
error msg, "the microsoft jet database engine cannot find the input table or
query 'SELECT rank from q1 WHERE bal <1'."

also, if i then close q2 and then reopen the "(" in front and back of
"SELECT rank.....WHERE bal <1) change to "[". from this point if i run q2 i
get the same error msg, "the microsoft jet database engine cannot find
.........SELECT rank from q1 WHERE bal <1'."

i copyied the sql from my q2 query for you to look over. (although the q2
SQL came from copying and pasting what you posted for me.)

did you/could you actually try your sql for q2 to see if it works for you??

again, thank you so very much for your help.

 

[Michel Walsh]

hi,



The first error is related to the fact I missed the GROUP BY clause.


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


I also add the optional argument of Nz.

The second error seems related to the fact that Access does not find the
query q1. Have you saved the first query under the name q1? if not, replace
q1, in this second query, by the exact name you used for the first query
you already saved.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

YES! it works. thanks for not giving up on me.

one last thing if you wouldn't mind.

could you explain the logic.

in excel i would use i.e. in cell b2 i would have the formula
"if(a2=0,0,a2+b1).
simple and logical (to me anyway).

but i don't have a clue how your sql got the results i needed.

could you explain the what/why of q1 and q2.

thanks again

hi,



The first error is related to the fact I missed the GROUP BY clause.


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


I also add the optional argument of Nz.

The second error seems related to the fact that Access does not find the
query q1. Have you saved the first query under the name q1? if not, replace
q1, in this second query, by the exact name you used for the first query
you already saved.


Hoping it may help,
Vanderghast, Access MVP

Hi Michel

thanks again for trying to help.

when i copy and past your SQL into q2 and run by selecting datasheet
view(before saving) i get an error msg,"you tried to execute a query that
does not include the specified expression 'date' as part of an aggregate
function."

if i save q2 and then run by selecting datasheet view i get a different
error msg, "the microsoft jet database engine cannot find the input table
or
query 'SELECT rank from q1 WHERE bal <1'."

also, if i then close q2 and then reopen the "(" in front and back of
"SELECT rank.....WHERE bal <1) change to "[". from this point if i run
q2 i
get the same error msg, "the microsoft jet database engine cannot find
........SELECT rank from q1 WHERE bal <1'."

i copyied the sql from my q2 query for you to look over. (although the q2
SQL came from copying and pasting what you posted for me.)

did you/could you actually try your sql for q2 to see if it works for
you??

again, thank you so very much for your help.

 

[Michel Walsh]

Hi,


In a table, not a recordset, but in a table, the records are not in a fixed
position. Else, inserting a record would eventually move a million of
records in a table of a million of records. So, the only "order" we can
really relay on is an order defined through the VALUES of the fields.

The first query consider for each record ( AS a ) how many records in table
aliased AS b, how many records, COUNT(*), in b, have a date before or equal
to the one in table aliased as a. Sure, since a and b are, the same table,
that COUNT(*) is then the rank, as in first, second, third, .... All that,
based on the value of the fields (the field date, in fact).


The second query keep only the ranks (as previously computed) where bal <0.
Next, among all those so kept, again, keep only those that correspond to a
date less or equal to the one supplied by the alias "a", and keep the max
value of these records from b.

Example:


a.date a.bal a.rank b.rank
xxx + 1 ---
xxx + 2 ---
xxx - 3 3
xxx + 4 ---
xxx + 5 ---
xxx - 6 6
xxx + 7 ---




where --- indicates the record in b is not kept because b.bal>0


Now consider the record with rank =4. The max(b.rank) with a rank <=4 is 3
(ie, the max(b.rank) from all records that precede it. SO, if we do the
same for all records, we got:



a.rank b.rank Nz(Max(b.rank), 0)
1 --- 0
2 --- 0
3 3 3
4 --- 3
5 --- 3
6 6 6
7 --- 6


where the MAX occur only on the records having b.rank less or equal to
a.rank.


Subtract column 3 from column 1 to get what you want.



Hoping it may help,
Vanderghast, Access MVP

YES! it works. thanks for not giving up on me.

one last thing if you wouldn't mind.

could you explain the logic.

in excel i would use i.e. in cell b2 i would have the formula
"if(a2=0,0,a2+b1).
simple and logical (to me anyway).

but i don't have a clue how your sql got the results i needed.

could you explain the what/why of q1 and q2.

thanks again

hi,



The first error is related to the fact I missed the GROUP BY clause.


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


I also add the optional argument of Nz.

The second error seems related to the fact that Access does not find the
query q1. Have you saved the first query under the name q1? if not,
replace
q1, in this second query, by the exact name you used for the first query
you already saved.


Hoping it may help,
Vanderghast, Access MVP

Hi Michel

thanks again for trying to help.

when i copy and past your SQL into q2 and run by selecting datasheet
view(before saving) i get an error msg,"you tried to execute a query
that
does not include the specified expression 'date' as part of an
aggregate
function."

if i save q2 and then run by selecting datasheet view i get a different
error msg, "the microsoft jet database engine cannot find the input
table
or
query 'SELECT rank from q1 WHERE bal <1'."

also, if i then close q2 and then reopen the "(" in front and back of
"SELECT rank.....WHERE bal <1) change to "[". from this point if i
run
q2 i
get the same error msg, "the microsoft jet database engine cannot find
........SELECT rank from q1 WHERE bal <1'."

i copyied the sql from my q2 query for you to look over. (although the
q2
SQL came from copying and pasting what you posted for me.)

did you/could you actually try your sql for q2 to see if it works for
you??

again, thank you so very much for your help.

 

[Guest]

thanks a lot.

Hi,


In a table, not a recordset, but in a table, the records are not in a fixed
position. Else, inserting a record would eventually move a million of
records in a table of a million of records. So, the only "order" we can
really relay on is an order defined through the VALUES of the fields.

The first query consider for each record ( AS a ) how many records in table
aliased AS b, how many records, COUNT(*), in b, have a date before or equal
to the one in table aliased as a. Sure, since a and b are, the same table,
that COUNT(*) is then the rank, as in first, second, third, .... All that,
based on the value of the fields (the field date, in fact).


The second query keep only the ranks (as previously computed) where bal <0.
Next, among all those so kept, again, keep only those that correspond to a
date less or equal to the one supplied by the alias "a", and keep the max
value of these records from b.

Example:


a.date a.bal a.rank b.rank
xxx + 1 ---
xxx + 2 ---
xxx - 3 3
xxx + 4 ---
xxx + 5 ---
xxx - 6 6
xxx + 7 ---




where --- indicates the record in b is not kept because b.bal>0


Now consider the record with rank =4. The max(b.rank) with a rank <=4 is 3
(ie, the max(b.rank) from all records that precede it. SO, if we do the
same for all records, we got:



a.rank b.rank Nz(Max(b.rank), 0)
1 --- 0
2 --- 0
3 3 3
4 --- 3
5 --- 3
6 6 6
7 --- 6


where the MAX occur only on the records having b.rank less or equal to
a.rank.


Subtract column 3 from column 1 to get what you want.



Hoping it may help,
Vanderghast, Access MVP

YES! it works. thanks for not giving up on me.

one last thing if you wouldn't mind.

could you explain the logic.

in excel i would use i.e. in cell b2 i would have the formula
"if(a2=0,0,a2+b1).
simple and logical (to me anyway).

but i don't have a clue how your sql got the results i needed.

could you explain the what/why of q1 and q2.

thanks again

hi,



The first error is related to the fact I missed the GROUP BY clause.


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


I also add the optional argument of Nz.

The second error seems related to the fact that Access does not find the
query q1. Have you saved the first query under the name q1? if not,
replace
q1, in this second query, by the exact name you used for the first query
you already saved.


Hoping it may help,
Vanderghast, Access MVP


Hi Michel

thanks again for trying to help.

when i copy and past your SQL into q2 and run by selecting datasheet
view(before saving) i get an error msg,"you tried to execute a query
that
does not include the specified expression 'date' as part of an
aggregate
function."

if i save q2 and then run by selecting datasheet view i get a different
error msg, "the microsoft jet database engine cannot find the input
table
or
query 'SELECT rank from q1 WHERE bal <1'."

also, if i then close q2 and then reopen the "(" in front and back of
"SELECT rank.....WHERE bal <1) change to "[". from this point if i
run
q2 i
get the same error msg, "the microsoft jet database engine cannot find
........SELECT rank from q1 WHERE bal <1'."

i copyied the sql from my q2 query for you to look over. (although the
q2
SQL came from copying and pasting what you posted for me.)

did you/could you actually try your sql for q2 to see if it works for
you??

again, thank you so very much for your help.