Calculating Eddington Numbers

[lord_lordy]

In my training diary I am calculating Eddington Numbers. For a given numeric field this is the maximum number X such that there X or more occurrences with a value of at least X.

I've got this working for Life To Date figures - this means you look at the full set of values. This has been done by having a script that counts the occurences greater than a given value X and also counts for value X+1. It uses fields in the Top level table (for which there is only one row) to maintain the counts.

Now I am trying to do this to calculate the numbers for each year. This means it looks for that value X for the subset of days defined by each year.

I have a related table which has a row for each year which is related down to days (via a months table). I've tried using the same method and it calculates it right for the given year but when it does that the counts for all the other years change to random figures.

I can't see why in my script and I don't have the advanced version so I can't debug.

Hoping someone here might have some ideas as to what might cause this so I can investigate.

This is my first Database ... so my experience is v limited.

[bruceR]

"I can't see why in my script and I don't have the advanced version so I can't debug."

And we don't have a copy of your script at all. Don't think anybody can help with what you have provided so far.

Please post a copy of the file; or clone. Or at least print your script to PDF and post the PDF; or copy the text out of the PDF and post that.

Edited September 12, 2010 by Guest

[comment]

For a given numeric field this is the maximum number X such that there [are] X or more occurrences with a value of at least X.

What should the result be when you have:

20 occurrences of 25

20 occurrences of 20

20 occurrences of 15

There are less than 25 occurrences of 25, so the number must be lower.

There are 40 occurrences with a value of at least 20 - so 20 is the highest number that meets the condition.

However, you will get the same result for a set of:

20 occurrences of 20

20 occurrences of 15

which means that the 20 occurrences of 25 contribute nothing to the result.

[lord_lordy]

I am not iterating to find the result. Instead the user can type in a suggested value and a trigger runs the script which calculates the count for that value and the next. Once the result is found then there's only need to monitor the next value to see when the value has gone up.

Didn't post the script as wasn't expecting anyone to debug for me ! Thought there may be some typical things that may cause it.

Attached is the script. Can attach the whole DB if someone really wants to help that much.

EdNum_Script.pdf

[lord_lordy]

which means that the 20 occurrences of 25 contribute nothing to the result.

Thats right. In this context it's what makes this metric interesting. So this was length of bike rides it would mean 20 rides of at least 20 miles. 1 more ride of 21 in this case would take it to 21, 5 of 25 would take it to 25 but to get to 26 would require 26 rides of 26 miles or more. ie the number gets progressively harder to improve.

[bruceR]

Didn't post the script as wasn't expecting anyone to debug for me !

Yes, please attach the whole file. It is generally better to provide more rather than less information when asking questions. We really can't know what you're doing with your data structure or scripts without seeing them. Without that, it's a lot like saying, "well I tried something and it didn't work."

[comment]

I've tried using the same method and it calculates it right for the given year but when it does that the counts for all the other years change to random figures.

I can't see anything in the script you posted that would cause that.

[lord_lordy]

I think I've worked out a better way to do this - set up a relationship using a ">" so that I can just count all the related records.

This works fine when the field I'm using is a number but doesn't work when it's a field calculated from related fields. Am I right in thinking that such relationships can't be constructed ?

[comment]

This works fine when the field I'm using is a number but doesn't work when it's a field calculated from related fields. Am I right in thinking that such relationships can't be constructed ?

Yes (if by "the field I'm using" you mean the matchfield in the "related" table). But I don't see how (or why) you got to this point. IMHO, once you have created the correct found set - either by finding or by GTRR - all you need to do is sort the records and loop through them. IIUC, the found set is created from a date range, so where's the "field calculated from related fields"?

[lord_lordy]

thanks for that. I think the penny may have just dropped ... never thought about sorting them.

As an aside I am finding moving this to a database enthralling largely because I can see a steady change in the way I think about problems and there's several occassions where I suddenly realise I should use a relationship and the problem becomes easy !

thanks for all your help.

[lord_lordy]

The sorting method works great.

I want to move it to a subscript which gets called with the field to be used as I have to do this calculation on 50+ fields.

How to I get the value of a field using the name of the field. There's a set field by name but doesn't appear to be the equivalent get field value by name.

Am I missing something?

[lord_lordy]

IGNORE last post

Almost immediately on posting that I found what I was looking for. No idea why I hadn't seen it before.

[lord_lordy]

(hopefully) final problem.

How do I sort on the appropriate field when I'm using a subscript which has the field passed as a parameter ?

My best solution is to have a separate field in the table for sorting and the subscript will copy the contents of the named field into that and then sort on the sort field.

Is there a better way of doing this copy other than looping through the whole table and copying each individually ?

[comment]

Can you explain this part:

I have to do this calculation on 50+ fields.

[lord_lordy]

It was really just highlighting that I wanted the logic separated in to a subscript so it wasn't repeated in 50 different places. Just easier to maintain.

I've managed to get it to work by passing in the name of the field to do the calculation on.