Sum the # of occurences of a value list value.

[sebastes]

This is a followup to a post I placed in the "define fields" forum. I still have not been able to get a workable solution to my problem described below:

In the database that I've created, i have a Table of Samples related to a table of Labs via the Lab Field. Each sample is collected in one of 6 container types that I have listed in a container field with a value list. The containers are supplied by the Labs. I would like to sum how many containers of each type I have in my sample table and have that value reported on my Labs table. In other words, Lab X needs to send me Y 1-gallon jugs.

I could not find an easy calculation to do this. Is there a way to do a conditional sum of non-numeric data? If I need more than 1 type of container from a Lab should i have multiple summary calculation fields in the Lab layout?

Any suggestions?

Thanks,

Ed

[ESpringer]

Since you're using FM7, I'd just whip up a table dedicated to container types.

I couldn't tell whether your six container types were each specifically from a particular lab (all one-gallons go with Lab A transactions, all 50-ml go with Lab or whether one-gallons for Lab A need to be counted separately from one-gallons for Lab B. Anyway, you'd want a record dedicated to each kind of container (like one-gallons for Lab A), and then just set up a relation between the Capacity field there and the Capacity field in the sample record, and ALSO match lab-to-lab. (If there's no overlap, you'd just have six records.)

Your particular Container-types should have a field specifying their lab-connection, so that it would be easy to see in a portal, while looking at a record for Lab A, that you've got 2 samples in 1-gallon jugs for them, and 30 samples in 50-ml tubes, or whatever...