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!

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

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

See attached for a fairly simple method.

SumFoundSet.zip

Thanks, -Queue-! That did the trick.

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!!!!

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

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 .