Calculating fields through the entire database

[Kroutchenkov]

I have a database that holds about 150 records. There is a repeating field that holds the stats for each of the 52 weeks of the year, that is to say that each record can be "Full", "Part", "On the road" and/or "Absent". These elements are in checkboxes.

It's easy to calculate, as an example, how many times an employee has been "Full". That's OK for me.

But how can I calculate how many employees has been "Full" for each Week without having to define a field for each week that would look like that

FIELD1 --------- GetRepetition(Week, 1)

Then creating another that will count the number of "Full" in the FIELD1 :

FIELD2 --------- PatternCount (Field1, "Full")

Then creating a Summary field that will make the Total of all the 1 for the FIELD2?

Is this possible to use the PatternCount function so it will calculate a repetition from a repeating field through the records of the entire database?

Thanks

[Vaughan]

The best bet would be to do away with the repeating fields and use a related file, where each record is the status of each employee and the week of the year it applies to. Getting your stats will then be a matter of performing a find.

[Kroutchenkov]

I know I should get rid of those repeating fields, but what if I won't? Or would it be possible to create a field which is the merge of all "Status" fields from the database?

[CobaltSky]

Well I have to agree with Vaughan that your use of repeating fields is less than ideal. In fact repeating fields are one of the more awkward and inefficient ways to go about structuring a database of the type you've described.

Notwithstanding that, data summaries can be achieved with repeating fields if you insist, and in a way which requires only one calculation field and one summary field for each category. That is, you will require one calculation field each for 'Full', 'Part', 'On the road' and 'Absent' plus a corresponding summary field for each.

Each of the four calculation fields will require a formula along the lines of:

PatternCount(

GetRepetition(week, 1) &

GetRepetition(week, 2) &

GetRepetition(week, 3) &

GetRepetition(week, 4) &

GetRepetition(week, 5) &

GetRepetition(week, 6) &

GetRepetition(week, 7) &

GetRepetition(week, 8) &

GetRepetition(week, 9) &

GetRepetition(week, 10) &

GetRepetition(week, 11) &

GetRepetition(week, 12) &

GetRepetition(week, 13) &

GetRepetition(week, 14) &

GetRepetition(week, 15) &

GetRepetition(week, 16) &

GetRepetition(week, 17) &

GetRepetition(week, 18) &

GetRepetition(week, 19) &

GetRepetition(week, 20) &

GetRepetition(week, 21) &

GetRepetition(week, 22) &

GetRepetition(week, 23) &

GetRepetition(week, 24) &

GetRepetition(week, 25) &

GetRepetition(week, 26) &

GetRepetition(week, 27) &

GetRepetition(week, 28) &

GetRepetition(week, 29) &

GetRepetition(week, 30) &

GetRepetition(week, 31) &

GetRepetition(week, 32) &

GetRepetition(week, 33) &

GetRepetition(week, 34) &

GetRepetition(week, 35) &

GetRepetition(week, 36) &

GetRepetition(week, 37) &

GetRepetition(week, 38) &

GetRepetition(week, 39) &

GetRepetition(week, 40) &

GetRepetition(week, 41) &

GetRepetition(week, 42) &

GetRepetition(week, 43) &

GetRepetition(week, 44) &

GetRepetition(week, 45) &

GetRepetition(week, 46) &

GetRepetition(week, 47) &

GetRepetition(week, 48) &

GetRepetition(week, 49) &

GetRepetition(week, 50) &

GetRepetition(week, 51) &

GetRepetition(week, 52), "full")

...and each of the four summary fields will have to be defined to return the Total of the corresponding calculation field.

It ain't pretty and I can't say I recommend it, but it will work...

On another point, you mentioned that you are using checkboxes to gather the information about the weekly status. This may not be ideal, since by default checkboxes accept multiple values and therefore it would be very easy for users to inadvertently select (for example) both full and part for a given week. Radio buttons would be a better choice, since they will only accept multiple values if the shift key is depressed while clicking them (otherwise a new selection replaces the previous one). Just a thought...

[Kroutchenkov]

Well, I understand your calculation, but it will give the Total of "Full", "Part", "On the Road" or "Absent" for all the employees and for all the weeks. What I want is a calculation, if it is possible, that will only summarize the "Full" from the week 1 or the "Part" of the week 47, etc.

If I change the repeating field to create a relationnal database or an external table, will I be able to delete the data from that database when I delete a record in the main one?

Thanks

[Vaughan]

Yes. The option is in the relationship definition. You'll see it.

[TSilvan]

Its seems to me that you can do what you want using the kind of formula that CobaltSky has given, but just leaving off some of the PatternCount.

So PatternCount(GetRepetition(week, 5), "full") would give you a flag for the full entries for week five, for instance. And then a summary field to match would add up all the flags.