CARR HOT SHEET - June 2003

In this Issue

• Heritage Database 101/201 Grad Outlines a Quick CARR Story
• New Database Release: National Assessment of Educational Progress (NAEP)
• What Do I Do Now? - A Few Hints for your First CARR Story
• Getting Started Mapping - New Journalism Case Study Book
• Mapping in the News: Journalism Pre-Conference
• Access: How to "Count," "Total," and "Group By"
• Spreadsheets to Databases (e.g. Excel to Access)
• FAQ's: Mapping, Spreadsheet, and Database Tips
• Summer Intern "Mini" Database 101
• 2003 CARR Boot Camp Schedule (NPC and Heritage)
  

Heritage Database 101/201 Grad Outlines a Quick CARR Story

In his first CARR outing, New York Daily News education reporter Joe Williams used his skills to produce a "quick hit" story on the level of funding the best schools in New York City receive. Using test score data supplied by the city Department of Education in an Excel file, he created a database that merged test scores with another data set that listed school-by-school per-pupil spending.

The result was a story that noted that the best schools in the city were, on average, spending considerably less on each student than the citywide average.
Some findings were:

Of the 209 schools where students scored high enough to be exempted from a new citywide reading and math curricula, 71% spent less per pupil than the city average. Spending in the top elementary schools last year ranged from $18,383 per student at PS 87 in Queens to $7,898 at PS 83 in East Harlem. Overall, 76% of the top elementary schools spent less than the citywide average of $10,738. Middle and high school spending stuck closer to their averages - $10,064 and $9,225, respectively.

"Here in New York, people always argue that the reason the school system isn't as good as in the suburbs is because the suburbs spend more money," said Williams. "There are obviously a lot of issues that don't pop up in this simple crunching of data, but the story itself reminds readers that education reform is a whole lot more complicated than just spending more money."

New Database Release: National Assessment of Educational Progress (NAEP)

The recently released NAEP, also known as the "Nation's Report Card," is the only nationally representative and continuing assessment of what America's students know and can do in various critical subject areas like math, reading, writing, and science. Since 1969, assessments have been conducted periodically in reading, mathematics, science, writing, history, geography, the arts, and other fields.
This is a special-use database and The Heritage Foundation's Center for Data Analysis is one of the small group of research institutions nationwide certified by the U.S. Department of Education to access primary NAEP data. In fact our leading education analyst, Dr. Kirk Johnson, is very familiar with the database and loves helping journalist make better sense of the data. By making information on student performance-and instructional factors related to that performance-available to policymakers and journalists at the national, state, and local levels, NAEP is an integral part of the nation's evaluation of the condition and progress of education.
NAEP does not provide scores for individual students or schools; instead, it offers results regarding subject-matter achievement, instructional experiences, and school environment for populations of students (e.g., fourth-graders) and subgroups of those populations (e.g., female students, Hispanic students). NAEP results are based on a sample of student populations of interest.
If you're interested how your state tested compared with other states or the national average this is new and ideal database for you. Contact out experts and an added dimension of testing data will be available to you.
For more information, contact: Dexter Ingram at 202-608-6227 or at Dexter.Ingram@Heritage.org.

What Do I Do Now? - A Few Hints for your First CARR Story


OK, you've just graduated Database 101 and you're really looking forward to surprising your editor with a great CARR story. You want a story that's unique so you FOIA a local government database that you hope will provide your newsroom with quite a few data driven stories. More often than not you'll come across a few pitfalls while exploring your options. Here are a few hints that might make your transition to a data driven CARR journalist a bit easier.

1) Be as specific as possible as to what you want regarding the data.

• We've all received data in a variety of formats, database, spreadsheet and yes... even hard copy with no disk. If you can get the data in spreadsheet or database format it will save you, your intern, or a temp a lot of time. Instead of performing data entry on thousands of records and then having to check a couple times for any possible typos or other errors, you'll have your information in an already easy to use format that can be imported to either Excel or Access and then sorted and filtered accordingly.
• Make sure you request a codebook to discern what some of the agency encryption means. Often departmental codes are understood only by the agency and are only gibberish to us non-governmental types.

2) Always make a couple copies of the original dataset.

• Many of us have learned the hard way to not work from the original dataset. Just remember how long it took to get the data. Sometimes a FOIA dataset could take months to arrive. Don't be so eager that you work on the original and have no original reference in case of a possible mistake.

3) Narrow the scope of data to better fit your needs.

• If you have a nationwide database and you're only interested in Montgomery County, MD limit your dataset to just that. You can always compare your local analysis to the nationwide numbers as well.
• If you're only interested in a certain age range or income bracket, filter out everything else. This is extremely useful when looking at salaries over $100,000 or ages above 18 years old.

• Maybe the agency made a typo and forgot or added a character... or maybe your small CARR project has just turned into a bigger story.

• If there is a more experienced data driven journalist in your newsroom ask for advice or assistance. If not, don't hesitate to email the NICAR listserv or give us a call in Heritage's CARR center.

Hopefully this will help you avoid many of the pitfalls a typical first time CARR reporter falls prey to. Good luck and get started.
For more information, contact: Dexter Ingram at 202-608-6227 or at Dexter.Ingram@Heritage.org.


Getting Started Mapping: "Mapping the News," David Herzog's New Book of Journalism Case Studies

The majority of journalist receiving this Hot Sheet have read or seen how much mapping can add to any newsroom. While extremely useful, it can be a bit overwhelming initially.

That transition has just been made a bit easier. According to Stephen K. Doig, the Knight Chair in Journalism at the Cronkite School of Journalism and Mass Communication at Arizona State University:

"Mapping the News is more than a how-to for journalists interested in GIS-it's a convincing why-to. The well-illustrated case studies described by author David Herzog show the power of GIS applied to a range of compelling stories on disasters, elections, taxation, toxic waste, drunken driving, and other issues important to readers. For those ready to move beyond reporting anecdotes into analyzing data, this book is a great introduction to a powerful new tool."

One Week Until the San Diego ESRI Journalism Pre-Conference

ESRI, the company behind many mapping products, is hosting a GIS in Journalism meeting July 6, 2003, at the San Diego Convention Center in San Diego, California. Working journalists, academics, and students are invited to attend the event, as well as the ESRI International User Conference that follows, July 7-11.
The GIS in Journalism seminar will feature reporters who often use mapping in their reporting, as well as graphics artists who visualize news in compelling ways, and GIS professionals who create fascinating data. There will be hands-on training, demonstrations of ESRI technology, and tips on cartographic design at this event.
Reporters, editors, graphic journalists, and television producers will find these sessions especially valuable whether they are new to GIS or are more advanced users. For more information on the conferences or registering at a special journalist rate, complete the registration form [PDF-156 KB] and e-mail it to journalism_precon@esri.com.
Questions? E-mail Kris Goodfellow, former New York Times writer and now ESRI media industry manager.

Access: How to "Count," "Total," and "Group By"

You've just made a query from two tables. One table listed all the licensed restaurants in your city and another table listed all the health code violations in your city. The relationship was made using the restaurant license ID number as the link between the tables. Now you want to find out how many times a restaurant was cited and what was the total amount of its fines.
Complicated? Not if you follow these quick easy steps.
1. Go to the "Design" View of your Query by clicking the top left icon on your screen. (blue triangle)
2. Next click the Sigma icon at the top of the page. It's identified as "Totals" if you put your mouse over it. This adds a new option/row labeled "Total" to the left of your data fields.
3. Under the License ID number column, in the "Total" row click "Count." You'll have to search through the drop down options in the cell.
4. Under the Restaurant Name column, in the "Total" row click "Group By."
5. Under the Fine column, in the "Total" row click "Sum."
6. Now click on the exclamation point at the top of your screen. This is the run or execute function.
Now you should see all your data arranged in a way that counts every time a license number is found, lists the restaurant's name, and sums all its fines. All this is done within minutes.
For more information, contact: Dexter Ingram at 202-608-6227 or at Dexter.Ingram@Heritage.org.

Spreadsheets to Databases (e.g. Excel to Access)

The ability to use different data applications depending on the need of the project is something every CARR reporter understands and values. Two of the most important, yet fairly basic, applications are Excel and Access. Excel is great for making quick charts and writing formulas, while access is very useful in combining tables into a query and printing concise reports.
What if you want to reap the benefits of both products during your analysis? You've luckily received your data in Excel, skillfully performed a few semi-complicated formulas, and now want to import the data into Access. No worries, the following should make it fairly simple.
1. Open Access to the database you want to import your Excel table.
2. Under the "File" window, click "Get External Database" and then click the extension "Import."
3. The "Import" window will appear.
4. First select "Microsoft Excel" in the "Files of Type" drop down arrow on the bottom right of the box.
5. Next go to the drive directory in "Look in" drop down arrow at the top right of the open window.
6. Highlight the spreadsheet you wish to import and click the "Import" box on the right of the window.
7. The "Import Spreadsheet Wizard" will now appear.
8. First select the Excel worksheet you want to import. Make sure it appears on the bottom of the window. Click "Next."
9. If your first row in Excel contains the column headings click the box in the second window that asks that question. Again, the bottom of the window will show you what the imported file will look like. Click "Next."
10. The next step is to decide if you want to store your data in a new table or an existing table. Click the box next to the desired data site. Most of the time the new data should probably be stored in a new table. Click "Next."
11. Next you're given the option to rename the column headings. Once satisfied, click "Next."
12. Access will ask if you want a primary key added to the file that will number each record and remain part of the file. If this is done it's usually done while working in the original Excel Spreadsheet. If you don't want it just click the box "No primary key."
13. The final step is to label your new table and hit "Finish."
Congrats, you're done. Now you can begin working on that query you want. Good luck!
For more information, contact: Dexter Ingram at 202-608-6227 or at Dexter.Ingram@Heritage.org.

For more information, contact: Katie Jackson at 202-546-4400 ext. 5120 or at Katie.Jackson@thfintern.org.

"Mini" Database 101 Boot Camps Available:


Summer's almost here and many newsrooms will soon be filled with interns eager to learn all they can. It also means we're offering "mini" Database 101 training days again. We're always seeking new ways of providing CARR training for journalists in the most convenient possible way. Last year, we conducted Mini -i.e. condensed - Database 101 Boot Camps for journalism interns from Knight Ridder Tribune News and Scripps Howard News Service.
These mini boot camps provide highly compressed half-day introductions to Newsroom Uses of Excel and Access. This approach can be useful as an introduction to CARR for skeptical and/or time-pressed newsroom colleagues, too.
Also during 2002, we took the CARR Boot Camp on the road for the first time, providing two days of training for eight editors and reporters at the Army Times Publishing Co. Let us know if this sounds like something for your organization to consider.
For more information, contact: Dexter Ingram at 202-608-6227 or at Dexter.Ingram@Heritage.org.

2003 CARR Boot Camp Schedule at The Heritage Foundation

Computer-Assisted Research and Reporting:
Database 101 | 201 Boot Camps
Each camp is limited to eight students and teaches practical, real-world skills to help you find the news behind the PR stats and spin. Tuition is free and there are a limited number of fellowships to assist with travel and hotel expenses. Media organizations that do not allow employees to accept fellowships or free training should consider contributing to the IRE Endowment Fund instead as an alternative.
2003
September 19-20
November 14-15
Courses are taught by experienced CARR journalists and statistics experts who have worked with editors, reporters and researchers for such news organizations as AP, Scripps Howard News, Knight Ridder Tribune News, USA Today and ABC News.
For more information about enrolling and schedules, contact Mark Tapscott at 202-608-6155 or Mark.Tapscott@Heritage.org.