Summarizing a Portal's Value Lists

[bpcstaff]

Hello,

In a portal that lists attendees of a conference, each record has a value list that specifies whether the person will attend EventA, EventB, or Both.

I've been trying to summarize this information in my layout with two fields - the first to count instances of "EventA, Both", and the second to count instances of "EventB, Both" - and can't for the life of me figure out what to do.

I could list the calculations I've attempted, but I've a feeling that would be a waste of space and would prefer to start from scratch.

Any assistance would be greatly appreciated!

[Fitch]

This type of question comes up frequently. Here's what you do:

In the related table (attendees), create a calculated field for each possible value, e.g. let's call this "choice_is_A":

myField = "EventA"

Then in your parent table, create the calculated fields that summarize, e.g.

Sum( attendees::choice_is_A )

It may be that you'd be better off simply using three separate fields to begin with. Then you wouldn't need the calculated attendees fields. Actually you wouldn't even need a "Both" field since that would be apparent when both A and B were selected.

[comment]

Shouldn't it be:

myField = "EventA" or myField = "Both"

Note that the result must be Number in order to summarize.

you'd be better off simply using three separate fields to begin with.

It would be even better to have a related record for each Attendee/Event combination.

[bpcstaff]

Brilliant! Thank you, both of you! No more needing to bang my head on the wall over this one... It works like a dream.

[comment]

It works like a dream.

No. It's a fix of a problem that shouldn't have existed in the first place. What if someone wants to produce a list of attendees, sub-summarized by event? Your data structure does not allow that. Ideally, there should be an unlimited number of events and a join table between events and attendees.

Even a repeating field would have worked better than what you have - two repetitions, formatted as checkbox of 1 or empty, summarized by a single summary field. That's two fields in total, where you now have five (the selection field, two calculations to split the data, and two summary fields).

[bpcstaff]

Got it - great ideas; this is my first time designing a database, and I'm learning it all by scratch, so I do appreciate the help.

[bpcstaff]

But - couldn't I just perform a find request specifying the appropriate parameters to generate a list of attendees for the event(s)?

Edited June 19, 2008 by Guest

[comment]

For a single event at a time, yes. But not for both - you don't have enough records to list Adam twice: once under Event A, and once under Event B.

[bpcstaff]

Got it - thanks much.