Creating a Summary of Information in All Records?

[Calantha]

Hello,

I would like to create a layout that basically summarizes information from all of the records. For instance, when an individual inquires about the school we type in an "inquiry date" on their profile page.

ie. Inquiry Date: 8/16/2005

Is it possible for me to get a summary of how many inquiries happened per month to conduct some sort of average inquiries per month calculation?

Is it possible for me to make a calculation that is based on all records?

I would like to extend this to multiple aspects of the database, ie. Inquiry Method is a Value List (Email, Mail, In Person, Phone) and I would like to summarize the average Inquiry Methods... etc.

Any ideas?

[Reed]

The easiest way to do summaries is to use summary fields. Summary fields are based on the found set, so they summarize all records that are visible. If you place summary fields into subsummary layout parts and then sort the records by the field on which you wish to summarize (month for example) then the report will show records grouped by month with totals for each month.

[Calantha]

But is it possible to have a continually upgrading summarziation without having to find... A constant running total all on one page?

Inquiries by Month:

Jan - 20, Feb - 35, Etc...

?

[-Queue-]

You mean something like this?

Summarize.zip

[Calantha]

Yes! Something like that where the values stay the same for all of the records!

I know you can do this with Global Fields but I need to be able to do calculations!

[Calantha]

Hello again,

I'm back trying to accomplish this again.

So far how I've seen it possible is to create one field that's a calculation, so something like this:

If I wanted to see how many people became aware of us through google:

if (awareness = "google", 1, 0)

then creating a summary field which totals that field giving me a running total of how many people according to the if statement.

Is this on the right track?

[-Queue-]

awareness = "google"

will produce the same result, since it is a boolean statement.

You can then either use a constant relationship (field equal to 1 related to itself) and a calculation field of Sum(relationship::googleFlagCalc) or use a global text field with a value of "google" and relate it to the awareness field, then create a calc of Count(relationship::serial).

The latter will be faster, since only "google" records are related.

[Calantha]

If I am understanding correctly (which I have my doubts that I am) the relationship::serial would necessitate serial numbers for each of my records?

Because my records don't have serial numbers (i know, I know.. bad...)

[-Queue-]

You don't have to use a serial. Any field that is never empty will work just as well. If you use a field that can be empty, it will not return the current count.

[Calantha]

Hello again,

I am once again tackling this problem.

I recently solved it by creating a flag and then creating a summary.

ie. GoogleFlag source of awareness = "google"

GoogleSum Total GoogleFlag

This has worked fine but I have over 50 different sources and creating the flag and then the summary takes a lot of time.

I attempted the previous version that you mentioned, creating a global field with google and then a count field. But if I have over 50 sources of awareness would they not all require their own table?

In some cases where I would like to summarize, example: by country... there are so many countries is there an easier way to do this?

Thank you!

Edited December 1, 2005 by Guest

[comment]

See if this thread can help.

[Calantha]

Thank you. I have read that topic a number of times and still fail to grasp the concept.

When I look at the file I don't understand how you manage to have so little fields for 7 different types of payment...

[comment]

Those are repeating fields. Each type is assigned a repetition (according to its position in the value list). The summary fields are defined to summarize each repetition individually.

[Calantha]

Thank you!

I love learning new things like this.

Could you give me an example of what the calculation would look like?

For example, i have a value list "Source" which contains Google and Yahoo.

I want to know how many people used Google and how many people used Yahoo.

Edited December 1, 2005 by Guest

[comment]

The demo file is the example. Change the value list to reflect your choices, e.g. Google¶Yahoo¶Friend¶Advertisement etc., and it will work.

Oops: I have noticed your profile says FMP6. I have no idea if version 6 can summarize individual repetitions.

[Calantha]

Great, thanks.

I'm actually using 8... I just haven't changed it yet. Oops.

[Calantha]

One last question: what if I do not have a value list for an item.

For example inquiry date.

If I want to summarize inquiries per month and the inquiry date field is entered upon inquiry, not from a drop down.

Is this a case where you have to create an individual calculation for each one? This is what I have already and it works fine. I'm just curious if there are other ways.

[Calantha]

And now that I'm attempting to implement it, it seems I do not have a field corresponding to the "value" field in the example.

[comment]

The demo shows Count AND Sum. The Value field is just the data being summed.

This method is particularly suited to static value lists, because:

a) if a record belongs to more than one category, it is counted in each category;

: the number of values can be matched to the number of repetitions.

Dates have the unfortunate tendency to pile up as time goes by, so I don't think you'd want to use this for your per-month summary. I would go with relationships from a calendar-type viewer table, as discussed here - but mind you, that is a rather complex solution.

[Calantha]

Well I thank you for your help. I'm not a database developer, in fact, I know little to nothing about them but have been told to build our database...

I just picked up a FileMaker 8 book which may be helpful.

Again, thank you!