I have a FMP7 database of voting data that now contains these tables and fields:

tblVoteData: County, Location, Candidate, Votes

tblPrecinctData: County, Location, Precinct, RegisteredVoters

tblVoteData contains raw results for each election and now runs about 120,000 records. There is one record for each candidate in each location in each county.

On the other hand, tblPrecinctData contains a set of fixed information listing the number of registered voters in each precinct within each voting location in each county. Voting locations will have one or more precincts.

Here's what I want to do. I'd like to add a set of fields to tblPrecinctData for certain candidates, one field per canditate. So I would add fields for Bush, Kerry, etc. Then I would like to pull in from tblVoteData, for example, the actual number of votes Bush got in that particular County and Location.

So if Bush got 500 votes (as reported in tblVoteData) in Arkansas County at the Gillett City Hall (Location), I'd like to pull that number into my new Bush field in tblPrecinctData for all three precincts voting in the Gillett City Hall in Arkansas County. Then I'll do some calculations on those numbers.

I now have the files related by County AND Location.

How can I do this? Or if there's a better way, I'm certainly open to suggestions.

Thanks!

Brudderman

You have the information about votes cast already available in tblVoteData. So take the Bush record in tblVoteData for Gillet City Hall in Arkansas County. Now related to that record via your relationship of County AND Location you have the set of precincts within the location and hence their licensed voter numbers. So it should be fairly straightforward to put all these numbers onto a layout in a tblVoteData occurrence for viewing and they will be also be available for use in calculation fields. The advantage of this is that you do not have to decide how many and which candidates you want to consider because any candidate who received votes can be used.