If you’re working from a Mac computer or getting into truly large datasets, Access may not be a viable option as a database manager. (It doesn’t work on Macs, and there are row limitations.) A good alternative is MySQL, an open-source database manager, which Alex Richards of the Chicago Tribune taught on Friday.

MySQL essentially installs a server that runs in the background on your computer. But if you don’t really understand what that means, don’t let it scare you. There are a handful of secondary applications that let you interface with MySQL in a way ...

Read more ...

I became interested in pipeline data after reporting on the Keystone XL oil pipeline. There was (and still is) a lot of debate about the pipeline's projected spill rate and safety. TransCanada, the Canadian company behind the project, already has one U.S. pipeline, which leaked 14 times within its first year of operation. I didn't know if that was unusual, so I wanted to compare TransCanada's record to the leak rates from other companies.

That story eventually proved too much to tackle, but it led me to another story about leak detection. As it turns out ...

Read more ...

Dead people have been giving us headaches at the NICAR Database Library. Our effort to update the Social Security Death Master File this year was quite a battle. Emerging victorious, one warrior has helped lead the way: the INSERT IGNORE INTO Structured Query Language (SQL) statement.

The Death Master File (DMF) is a gigantic database with nearly 86 million records as of the most recent release. In 1983 Congress required the Social Security Administration (SSA) to collect records of deaths from states, which were then stored in the newly-created DMF. The database contains deaths going back decades. Each record in ...

Read more ...

Until recently, getting arrested in Philadelphia for possession of crack cocaine and admitting drug dependency would not preclude being hired or continuing to work as a police officer in the public school system.

A month-long, data-driven investigation  by The Philadelphia Inquirer found that in more than a dozen cases school police were themselves getting into trouble with the law. Even an open bench warrant issued for one officer charged with a drug offense failed to trip the school district's alarm.

In another case, an officer who showed up in court to face charges after her second arrest for drug ...

Read more ...

New York Times reporter Barry Meier knew lawsuits against the manufacturers of all-metal artificial hips were on the rise. But it wasn’t until I queried a balky Food and Drug Administration database that he was able to confirm that all-metal hip implants were quickly becoming the biggest and costliest medical implant problem since Medtronic recalled a widely used heart device in 2007.

The FDA collects voluntary reports from patients, health care providers and medical device manufacturers about problems experienced with specific devices. The federal agency compiles the reports of deaths, injuries and product malfunctions in a database known as ...

Read more ...

Every day, thousands of Muskegon County residents pour into their favorite restaurants to dine with friends and family. Yet until now, little was known about whether those restaurants followed practices aimed at preventing people from getting sick.

While looking through more than 22,000 electronic health-inspection records spanning four years, I found numerous instances where restaurants repeatedly violated rules that help prevent foodborne illness. Schools, hospitals and food stands were cited for breaking the rules, too.

Raw chicken and crabmeat sitting out at room temperature. Food kept past its expiration date. Cockroaches, mice and fruit flies living in kitchens. Employees ...

Read more ...

Your average CAR geeks - especially the old timers - follow a predictable route in tools they use for data analysis and sharing.

You start with Microsoft Excel spreadsheets, and everything’s going fine. But then someone tells you about relational databases, and suddenly you notice all of the things you can’t easily do in Excel.

Step up to Microsoft Access database manager and pretty soon you’re joining tables right and left, slipping terms like "Group By" and "normalization" into conversations and generally feeling pretty good about yourself.

But at some point, someone in your newsroom looks over your shoulder ...

Read more ...

When the California legislature passed a law to drastically increase funding to nursing homes, it came with a promise that worker wages would rise, staffing would soar and patient care would improve.

The law passed in 2004. When I started working on investigative articles for California Watch in the fall of 2009, it seemed like a good idea to take a close look at whether the promises attached to hundreds of millions of dollars came true.

What we found was noteworthy. State and federal funders poured an additional $880 million into nursing homes over five years, moving the annual funding ...

Read more ...

Folks doing CAR are blessed with a wealth of tools, which is both a blessing and a curse. For example, I frequently use two database programs, MySQL and PostgreSQL, at work. While similar in most respects, they have slightly different syntax for some common tasks such as string functions. You see this situation played out on the NICAR-L listserv all the time, when someone asks a question that usually starts with, "I know I've done this before, but I can't seem to remember the right syntax." Keeping snippets of SQL or Excel IF statements lying around in files ... Read more ...

In most cases, when you want to match data from one table in a database to another, a common approach is to use a JOIN clause in your query. For example, if you had a list of candidates in one table, and a list of donations made to candidates (with one field having a candidate ID in it), you'd probably either use JOIN clause or a WHERE clause to match them together: (pseudo-code) SELECT * FROM candidates, donations where candidates.candidate_id = donations.candidate_id; But simple joins aren't the only technique available to journalists trying to learn more about their ... Read more ...