Sum of a type

[mdc1952]

I have a question that is beyond my FM knowledge. I have a database whose function is esoteric though similar in structure to more normal ones. It consists of samples of charcoal recovered from prehistoric arhcaeological sites. Each sample has numerous types of charcoal (wood, nut shell, nut meat, etc.). There is a table for the samples with information about them, a data table with counts and weights of the charcoal types (related to samples with a sample ID code), and a table of types related to the data with a type code.

It is easy to get a total of the charcoal for each sample in a live display in a layout using a calculation field that sums the amount or weight from the data table for each sample. However, is it possible to get a live display of a particular type of charcoal in a sample? I can't figure out how to do it, though I can script it by doing a find on the data table for each sample and the type.

I can also probably do it in a report, but I don't want that. I need the value to figure into other calculations, such as ratio of wood to nut shell charcoal. The scripted route is ok, but I was wondering if there was another way.

Thanks for any help.

[John Mark Osborne]

What you'll need is a relationship for each charcoal type. Start by creating a calculation for each charcoal type in the sites table. These calculations will simply equal the name of the charcoal type as it appears in the types table. Relate each one of these charcoal type calculations starting from the same table occurrence based on the sites table to different table occurrences based on the types table. Now you will be able to use Aggregate functions to total the types in a calculation field. For example:

Sum(TypeWood::Weight)

or

Sum(TypeNut::Count)

[mdc1952]

Thanks. I see what would be needed, but I think I'll stick with a script. This would create a great maze of relationships and TOs given the nature of the data. There are four sets of data per sample (general data, seed types, wood species, and noncharcoal) each with 20-50 potential types, and new types can be added during data entry. I think I'll keep it simple.

[John Mark Osborne]

I completely agree. The standard method for producing reports like you want is to use a subsummary part on a layout. This allows for maximum flexibility. For example, if you add or change a new type, you would need to add or change a relationship. With a subsummary report, it would adapt automatically. In addition, subsummary reports also allow you to modify the report by changing the found set. Relationships require you add additional key fields and possibly complicated scripts to modify the records being summarized.

You could easily place a button on the layout that opens a new displaying the subsummary report. It will be much faster than a scripts that summarizes by looping through the records.