Occurence since....

[thechump]

Hi all, I was wondering if anybody has any ideas about the following problem I am trying to solve. I have a field containing the values 0 and 1. I would like to produce another field that calculates the number of times since the value 1 occurred. For example..

Field 1 Field 2

0 1

0 2

0 3

1 0

0 1

0 2

1 0

I hope this makes some sense and any nudges in the right direction would be greatly appreciated.

Andy

[IdealData]

calculates the number of times since the value 1

The number of times of what?

[thechump]

Sorry, I didn't make myself very clear. I would like to calculate the number of occurences of the value 0 since the last occurence of the value 1.

Thanks

Andy

[comment]

Can you explain the purpose behind this? The "last" occurrence is a function of the sort order and - optionally - of the current found set. That is assuming the rows in your example are records, not multiple values in the same field.

[thechump]

The database is to record pupil progress in a set of tests. The idea behind it is that each time a child answers a question correctly it is recordrd as a 1. When incorrect a 0. I want to calculate the number of times an answer is given before it is answred correctly. These tests are completed over a lenghthy period of time, in reality each entry to the field happens weekly. My hope would eventually to be to complete another calculation that will give me an overview of the number of 1's in the last 10 entries, last 50 entries etc.

Edited February 14, 2010 by Guest

[comment]

Here's one possible way to look at it. Note that this requires entries to be made in chronological order (or a relookup needs to be performed).

TallyLU.fp7.zip

[thechump]

Thanks for the swift response. This is just the thing I needed but... is it at all possible to recalculate the final two fields as we have sometimes found that human errors when entering the 0's and 1's hence we would need recalculation if a result changes.

Thanks

Andy

[comment]

Sure: find all affected records (i.e. records "later" than the modified record - or just show all records), click into one of the matchfields (ResponseID or RespondentID or QuestionID) and select Relookup Field Contents. This process can be scripted and attached to a button, if you need it often enough.

[thechump]

Thank you so much for your time on this. Briliant help!

My next task is to try to perform the analysis on only the last 10, 50 or 100 entries etc. Is it possible to select a particular number of records from a found set?

Edited February 15, 2010 by Guest

[comment]

The calculation itself does not depend on the found set. To show only the last n entries, do a script:

Show All Records

Go to Record [First]

Omit Multiple Records [ Get(FoundCount) - n ]

[thechump]

Comment, thank you for all your help on this.