Is there a way to get a sum of all the records in a database with a specific value in a specific field without having to go to find mode. For example, I am creating a stats page for the database I am working on. On this stats page I want to know the total number of records that have the color field set to red and how many records have it set to blue. Is there a way to auto-enter this info in rather than going to find mode searching, copy, paste.

Heathbo

Here's how I'd do it (i've attached an example)...

Make a self-joining relationship with a global set to "1", or between two tables, doesn't matter.

Then on the table your report will use, make a calculation for each color you want to report on:

colors.fp7.zip

Thank you, I've got it working now just perfectly. I have one more question. What if I wanted to add more values that are not in the same value list. For example: There is another value list(Flower, Fruit). I wanted to know the sum of all the records that are Red(in one drop down list) and Flower(in another drop down list). I assume its a small modification to the sumRed field calculation.

Thanks for your time

Heathbo

Using the same logic, you could have a "FlowerIsRed" calculation field, =

Case ( color="red" and Type = "Flower" , 1, 0 )

Then Sum that field, using your constant self-join relationship.

There is another more flexible method, if you were wanting to get a sum from choices the user was making at that moment. But the above is probably the fastest and best for reports. Hopefully you have only a few choices, as the combinations will multiple rapidly.

I wonder whether, with a lot of combos, you mighty not be better off doing this -

Define Result [Calculation] = Left(Colour, 1) & Left(Type, 1)

and Criterion as a global, set to contain what ever combo you want.

Define a self-join Criterion to Result.

Then use Count(SelfJoin::Result)

You might need to fiddle with the definition of Result to account for 'Black' and 'Brown', etc.