Now that Chad-Fest 2000, otherwise known as the presidential election is over – I suppose I could call it a Chad Row, but awful puns like that are just so tacky – it might be a good time to come back to earth and think about the kinds of elections we normally cover.
Small stuff: town council, school board, county commissioner, maybe governor.
And lots of computer savvy reporters figured out a long time ago that a spreadsheet is an election reporter's best friend, ideal for tabulating votes, percentages and winners and able to produce neat little graphs lickety-split.
But as soon as results are in, the questions start coming:
What happened? How'd she win? or How'd the Black community/Hispanic neighborhoods/the rich/the poor/home owners/condo residents/gays/swing precincts vote?
Usually that means making a quick checklist of key precincts, scanning the spreadsheet and tweaking out whatever patterns may be visible. It's not easy and there's a better way. A map of the election will often let the patterns jump out at you and your colleagues whose eyes may glaze over when they look at a table of numbers. It's not hard, but to make it happen requires some front-end work before the election. I call it the "Election Machine."
You'll need:
• Excel, Access and ArcView or some other spreadsheet, database manager and mapping program.
• Precinct-by-precinct voter registration data, in the spreadsheet.
• Tables of key voting precincts, in the database manager.
• A map layer showing election precincts.
• Election results in the spreadsheet.
Here's a step-by-step guide to how it all fits together using Excel, Access and ArcView. Looking at real examples will make it a little easier to follow; if you want to try it for yourself, download a PowerPoint demonstration and sample data in Excel, Access and ArcView from the NICAR Web site: www.nicar.org/electmach/index.html.
Step 1: Voter Registration
Build a one-page workbook showing the number of Democrats, Republicans, etc. in each precinct or election district, as well as the number of blacks, whites, Hispanics and any other ethnic or demographic information you can obtain. The best source for this usually is the county supervisor of elections, who should keep this information on each precinct; other sources might be local political consultants or university political scientists or demographers. Figure the percentages of each political or demographic group: Democrat, Republican, black, white, etc.
Step 2: Key Precincts
Create a database and build a table identifying key precincts, bellwether precincts that are predominantly Democratic, Republican, Black, white and the like.
Step 3: Election Results
Build a spreadsheet workbook with one page for overall returns, precinct-by-precinct and a separate page for each election that you want to analyze. Why? If you're lucky, you can get results in some electronic format, through e-mail, a download or on a floppy disk.
Ask the election folks for a dummy file before – the same layout you'll see on Election Day, but without numbers – and build a dummy page mimicking that layout. Then it becomes a (reasonably) simple matter to paste the results onto the page. Try this in advance to make sure it works. Worst-comes-to-worst, build the page in advance and type results into it.
If you're dealing with multiple elections – several municipalities or commission seats, etc. – copy results for each election in which you're interested to separate pages, one page per election. Paste the results from the results page as a live link by clicking on "Edit," "Paste Special," "Paste Link." When you do it this way, the results of each individual election will flow automatically from the results page to the election pages.
Build formulas, calculating what you want to know about the election: the turnout, the winner, etc.
How Come...
• ...it's important to keep registration numbers in a separate workbook? Registration data will change election-to-election and you'll sometimes have to re-calculate percentages. Keeping it separate lets you build a registration template, updating it for each election as the numbers change
• ...I should keep overall election results on one page and each election on others? Because it lets you paste/type all the results on one page instead of having to do it on multiple pages. Because it lets you type all results on one page.
• ...I should keep key precincts in a database? Because key precincts are not likely to change and you can use the same base data for each election.
Step 4: Name Spreadsheet Election Pages
On each individual election's page, select the range containing the results, including headings. Give it a name – City Council, for example – by clicking "Insert, Name," "Define" and "Add." Do the same for the voter registration page. You'll see that the name of the range will appear in a small window left of Excel's formula bar.
Open Access and click "File," Get External Data," and "Link Tables." Select the election results workbook. When the Wizard opens, click on "Show Named Ranges." You'll see that the ranges you just named in Excel show up as items you can link to Access. Follow the wizard's instructions and link each Excel range to Access as a table.
Do the same in the registration workbook: Name the range containing the demographic percentages in which you're interested and link it to Access as a table.
Step 5: Build and Run Queries
Build queries to look at winners, losers, ethnic votes or anything else of interest. Link to swing precincts winner, Black vote or anything else of interest. Link precinct-by-precinct registration numbers to precinct-by-precinct results table or swing precinct table to determine voting patterns. You can build the queries in advance, linking to your dummy data. That makes things faster on deadline.
Step 6: Export Queries
Export the results of each relevant query in dBase IV (*.dbf) format by clicking on "File," and "Save As/Export." Choose "To an External File or Database" and follow the wizard. This will let you import the results into ArcView.
Step 7: Build Maps
Open ArcView, where you've already built a precinct map. You may be able to get a precinct map from your local election supervisor, city or county planning department, the state legislative staff responsible for redistricting, political consultant or political scientist may have one.
Join the *.dbf tables you exported from Access to the map.
The example here joins a "Winner" table to the precinct map. It shows that Rubble didn't get beyond his political base, while Flintstone took his base in Central Bedrock and more.
Summing up
Using a spreadsheet, database manager and mapping software you can quickly create a picture of what happened in an election. Just gather the basic data that you'll need in advance and build spreadsheets, tables and queries.
Build dummy spreadsheets for the election results you won't get until Election Day. When results