run a counter on records meeting a criteria

[jay123]

Hi all,

I'm new to access-programmers (and relatively new to access!!). I have an issue which i'm stuck on at the moment - and I cant seem to find a resolution!

I would like to run a counter on records within a query which meet a certain criteria. And if record meets a different criteria, then i would like to restart counting from zero.

For example, I would like the counter to increment by 1 for every record that is a 'Pass', then when a 'Fail' recorded the counter reverts back to zero. I'm basically trying to record how many 'Pass' marks an idividual gets in a row.

Im not sure if i'm barking up the wrong tree, but i have the idea that a loop statement would be the most suitable - however i'm not sure on the syntax??

Any help or a steer in the right direction would be much appreciated,
Thanks in advance.

 

[ajetrumpet]

Hi all,

I'm new to access-programmers (and relatively new to access!!). I have an issue which i'm stuck on at the moment - and I cant seem to find a resolution!

I would like to run a counter on records within a query which meet a certain criteria. And if record meets a different criteria, then i would like to restart counting from zero.

For example, I would like the counter to increment by 1 for every record that is a 'Pass', then when a 'Fail' recorded the counter reverts back to zero. I'm basically trying to record how many 'Pass' marks an idividual gets in a row.

Im not sure if i'm barking up the wrong tree, but i have the idea that a loop statement would be the most suitable - however i'm not sure on the syntax??

Any help or a steer in the right direction would be much appreciated,
Thanks in advance.

check this out:

http://www.access-programmers.co.uk/forums/showthread.php?p=938532#post938532

there is a section about using functions in access sql. also, what you have said really doesn't make sense. you want to open a recordset, loop it, test for one condition and increment the counter if met, then if it is not met, scrap the counter and start over. by doing this, what would the resulting counter at the end of loop tell you? I think you're wanting something different, but the explanation might be wrong here.

can you explain any better? someone could post a small function for you I'm sure, if they knew better about what it is you're after.

 

[jay123]

what you have said really doesn't make sense. you want to open a recordset, loop it, test for one condition and increment the counter if met, then if it is not met, scrap the counter and start over. by doing this, what would the resulting counter at the end of loop tell you? I think you're wanting something different, but the explanation might be wrong here.

can you explain any better? someone could post a small function for you I'm sure, if they knew better about what it is you're after.

Just to explain further. I need to show how many records people have passed in a row. when they hit 20, they are seen to be competant. However if they hit a fail, the process (or count) must start over again.

you may be right, a loop statement may not make sense, so i'm open to any ideas.

Thanks for your help.

 

[jay123]

Hi All,

Can anyone suggest any ideas for me regarding this topic.

Basically, i have a timestamp field (containing date & time) and a result field (containing 'Pass' or 'Fail'). I need to count the number of consecutive 'Pass' results. As when an idividual consecutively passes 20 tests records, they are seen competant. However, if an individual fails a record before the counter gets to 20, i need to restart the counter from 0.

Does anyone have any ideas?

Thanks in advance for your time.

 

[vbaInet]

Lookup the DCount function in the help files or google it. See if that meets your needs.

 

[jay123]

Lookup the DCount function in the help files or google it. See if that meets your needs.

I've tried this, but i cant think of a way to make the counter restart at zero after a 'fail' result.

 

[vbaInet]

GROUP BY the field you would like to run a total for, and COUNT it as a different expression.

 

[jay123]

GROUP BY the field you would like to run a total for, and COUNT it as a different expression.

I'm sorry for appearing dim here. but can you explain a little more how i would achieve this.

really appreciate your efforts on this.

 

[vbaInet]

Might be easier if you show an example of what you want to achieve. For example.
Let's say you have these records:


Field_A
101
101
120
120
120
140
141

Do you want to do this?
Field_A Count_Of_FieldA
101 1
101 2
120 1
120 2
120 3
140 1
141 1

OR:
Field_A Count_Of_FieldA
101 2
120 3
140 1
141 1

 

[jay123]

Might be easier if you show an example of what you want to achieve. For example.
Let's say you have these records:


Field_A
101
101
120
120
120
140
141

Do you want to do this?
Field_A Count_Of_FieldA
101 1
101 2
120 1
120 2
120 3
140 1
141 1

OR:
Field_A Count_Of_FieldA
101 2
120 3
140 1
141 1

Hi, it's more the 2nd option. but i need to show this:

FieldA Result Count
101 Pass 1
101 Pass 2
101 Pass 3
101 Pass 4
101 Fail Then the count restarts
101 Pass 1
101 Pass 2
101 Pass 3


so i could then sumarise this to show the current count for FieldA

FieldA Result Count
101 Pass 3

Hope i've explained this ok. and thanks again

 

[vbaInet]

By the way they are only two options. Are you sure you mean this option?:
Field_A Count_Of_FieldA
101 2
120 3
140 1
141 1

 

[jay123]

Yes, that option would be fine. I'm still puzzled how I would restart the count after a fail though.

 

[georgedwilkinson]

A query will do this for you. Just use a sub-query to find the date/timestamp of the last "Fail" and compare that to the date/timestamp of the main query within the where clause.

select stuff, count(*)
from mytable
where dtstamp > (select top 1 dtstamp from mytable where result = 'Fail' order by result desc)
group by stuff;

Obviously, this is air code and a bit simple. Also, didn't really test it out but it may require another sub-query or sub-sub-query. I show it only to demonstrate how you might implement a solution. If you can't figure out the syntax for your situation show us what you've got and what you've got to work with and somebody can help get it right.

 

[gemma-the-husky]

the thing is, access doesnt/cant easily determine an ordinal position in this way. databases are based on set theory - ie you get a set of data that meets certain conditions, and the order within that set is immaterial - all the items are just members of the set.

And the other important thing is that each item in that set is independent of the others per se - alll access usually knows about is the current record - so to actually do computations that involve evaluating the current item relative to the previous or next record is ALSO very difficult to do in access - because you have to force access to consider the items in a particular order

so - a REPORT has a natural sequence, and therefore its easy to show that sequence on a report. Everything else, table/query/form is different.

eg - lets say you determine an order postion (oridinal) for each row - now what do YOU expect to happen to this, if when displayed on a form you

a) use the filter command to selectively show some items or
b) reoroder based on any of the columns available.

.... and that's the problem in a nutshell. Your view of what ought to happen may be different to someone else's.

==========
and thats why its actually very difficult to do what you are asking - because basically in terms of a database, absolute position is a meaningless concept.

and in many ways, its appropriate as a developer to try not to think of data in that way. Think in terms of numbers of items in a group, but not in terms of the position of items within that group. eg

50 items for Jan

[even broken into
week 1 - 10 items
week 2 - 17 items
week 3 - 14 items
week 4 - 9 items]
(but never identifying a given item, as item no 16, say - or even caring about the position in those terms)

20 items for Feb
17 items for March


Hope this helps

=================
thinking about this again, maybe the issue is to consider how the data gets INPUT into the system in the first place, and to record the pass/fail count AT THAT TIME, ie when yuo can control it - and not afterwards.

 

[jay123]

A query will do this for you. Just use a sub-query to find the date/timestamp of the last "Fail" and compare that to the date/timestamp of the main query within the where clause.

select stuff, count(*)
from mytable
where dtstamp > (select top 1 dtstamp from mytable where result = 'Fail' order by result desc)
group by stuff;

Obviously, this is air code and a bit simple. Also, didn't really test it out but it may require another sub-query or sub-sub-query. I show it only to demonstrate how you might implement a solution. If you can't figure out the syntax for your situation show us what you've got and what you've got to work with and somebody can help get it right.

Hi George,

Couldn't see the wood for the trees!

I think i was over complicating it in my head! Great idea, works like a charm!

Thanks
Jay