Is this subsummary possible?

[Kent Searight]

Imagine there are 3 fields in a table, one called _ID (a number used for finding a set of records), one called _CATEGORY, a text field that allows entries to be made as strict entries from a value list, and one called _SUBSUMMARY_CATEGORY.

Now, imagine a found set of 10 records based on having a common _ID. Three records say "Box" in their _CATEGORY, two records say "Crate" in their _CATEGORY, and five records say "Drum" in their _CATEGORY.

When I view the individual records in browse mode I'd like to see the _SUBSUMMARY_CATEGORY field show me the sum of similar entries from the _CATEGORY field, but only the sum relating to that particular record. In other words, if I'm viewing any of the three records that say "Box", I'd like the _SUBSUMMARY_CATEGORY field to show me a "3", in any of the two "Crate" records I'd like it to show "2", etc.

I hope this makes sense (and is possible) Thanks in advance!

[Peter1]

Create a self-join between category. ie link category to category with 2 tables in the relationships graph. Create a calculation field (from context of the main table you mention) that counts the field CATEGORY. ie COUNT(CATEGORY). This value must not be unstored.

Peter

[Kent Searight]

Very close, it almost works...except the calculation field _SUBSUMMARY_CATEGORY returns a count value for all existing records with matching criteria, not just those in the found set.

Also, the calculation field doesn't immediately update if I make a change to the _CATEGORY field and then Tab out of the field. It only updates when I mouse-click out of the fields and onto the background.

Thanks for your help so far

[-Queue-]

See attached for a fairly simple method.

SumFoundSet.zip

[Kent Searight]

Thanks, -Queue-! That did the trick.

[Kent Searight]

Just when I thought there was smooth sailing ahead, another variable was thrown at me. Now the count reflected in the field I've called _SUBSUMMARY_CATEGORY must show the count of like _CATEGORY entries PLUS those that are deemed acceptable by the user. The acceptibility is determined by a calculation field that shows either a "1" (yes) or a "0" (no).

I've been experimenting with the self-join relationship that -Queue- showed me in the response above this since 12/30/04, in the hopes I could figure it out myself, but nothing seems to work. Can I not use a calculation as part of a self-join relation?

Any help is greatly appreciated!!!!

[-Queue-]

If the calculation can be indexed, then yes. See attached for example.

If the calculation cannot be indexed, then use the original relationship and change SumCat to be Sum(SumFoundSet 2::flagAcceptable).

SumFoundSetv2.zip

[Kent Searight]

Many thanks, queue! It took me a while to figure out why your example worked but when I did I had an "Ah Hah!" moment. As it turns out I had to use your second suggestion anyway since the calculation field couldn't be indexed. Either way, a lot was learned from your examples and advice, and all your help is greatly appreciated .