Hi there, crew - spent ages trying to figure this out, and I was
wondering if anyone here could help...

I'm building a database for a political organization, which is set up
to have a Voter table (with unique IDs, one per person), and a Votes
table (with one record per voter for each election for which they were
registered). There are about 200K voters, and 850,000 vote records.

What I want to do is calculate the vote totals per voter, so people
using this database can prioritize their efforts using vote frequency.

However, there are 3 different kinds of "votes" -- an absentee vote, an
"at the polls" vote, and a "no" vote (one for which a person was
registered, but didn't show up.) Using Count (Votes::VoterID) gives me
the number of related records for that voter, but what I also want is
something like Count (Votes::VoteCast = "Absentee") or Count
((Votes::VoteCast = "Absentee") + (Votes::VoteCast = "Polls").

This is, as I've learned, not a valid calculation in Filemaker, and I
was wondering how I might get this data (either dynamically or stored
in a "VoterStats" table that uses the same unique VoterID as the Voters
table). One approach I came up with was to create a separate table for
each kind of vote, ie Voter 5555555 would have 2 records in the
"Absentee Vote" table, 1 in the "No Vote" table, and 1 in the "Poll
Vote" table -- but this seems kind of hack-like to me, and creates
difficult error-checking problems anywhere else I'd be using the vote
records.

So... could anyone here point me in the direction of doing this
calculation properly? The DB is not out in the field as yet, so some
structural changes are possible, though obviously I'd like to keep it
to a minimum...

scv-matt wrote on (9/27/2005):

I generall like to test my suggestions, but I don't timeright now, and Iam
honestly too tired to try it, but I did something similar, so off the top
of my head...

1. Create a global field in the Voter table for each type of vote
(Absentee, Polls, whatever they're called in the Vote Table), and populate
each field accordingly. This is part of your relationship key to the Vote
Table.

2. Create a relationship for each type of vote, named accordingly, from
the appropriate Voter::GLobalType = Vote::Type AND Voter::VoterID =
Vote::VoterID.

3. Create a regular calculation (Count) field in Voter for each type of
vote, displaying the count from the appropriate relationship.

Each should show a count of the type of vote by each voter.

Matt

In article <1127872702.314705.86470 (AT) o13g2000cwo (DOT) googlegroups.com>,
"scv-matt" <matt.waggner (AT) gmail (DOT) com> wrote:

I know of a couple of ways to do what you want.

First way: define three new calculation fields, text result, in the
Votes table:
Absentee = If(VoteCast="Absentee";"x";"")
Polls = If(VoteCast="Poll";"x";"")
NoVote = If VoteCast="No";"x";"")

Then base your three Count fields on these three fields.

Second way: Do it by multiple relationships, based on each of the three
categories of votes. Put three global text fields with values
"Absentee", "Poll" and "No" in the Voter table, and base the three
relationships on these. Then change you Count formulas to be based on
these relationships.

Bill

--
For email, remove invalid.