Monday, January 21, 2013

Military pharmacy costs and summing across spreadsheets

I was thinking of a way to write about a story I helped with recently, Soaring cost of military drugs could hurt budget, but it wasn't really a technical masterpiece. Again, this is an example of a data-driven story where the best work was done in reporting.

Our attempt (Jeremy Schwartz and I) to find out how much the military spent on pharmaceuticals proved quite difficult. There are at least three methods they use: The Defense Logistics Agency buys drugs from pharmaceutical companies for use in military hospitals and pharmacies. The Department of Defense health care system, Tricare Management Activity, provide prescription services through retail pharmacies, just like other health care pharmacies. Tricare also offers members prescriptions through mail order drugs.

We were able to get pharmaceutical sales from DLA and Tricare, but not the mail order drugs. The records were difficult to work, as company names and drug names were not consistent. Cleaning up the data was more than adjust name ... the bulk came through research to find out what companies merged with others or were subsidiaries. That was just work. I created a new column in the spreadsheet and Jeremy and I each spent time doing research on company websites and other sources to find combinations as best we could.

Both the Tricare and DLA worksheets have the
same rows of companies in the same order,  as do the columns.
Once Excel trick I did use was to total cells from multiple worksheets. This isn't all that rare for those who use Excel often, but it might be something a general reporter may not know how to do, so I thought I would explain it here.

In my example, I have three worksheets in my spreadsheet file: DLA, Tricare, Combined. I had company totals by year in each column for both DLA and Tricare.

On the Combined worksheet, I used the following formula to add the companies together:


You are using the values of the B2 cell from another worksheet by prepending it with the name of the worksheet and the exclamation point.

You can then copy the formula to other cells, like any other formula.

Why can't we add notes mode to SQL in MS Access?

I had two stories I worked on run in the last 30 days where much of the work was done in the months before. It's at hard way to work ... to get pulled back into something and expect to answer questions about your analysis when you haven't looked at the data in weeks or months.

To help with this, I sometimes keep really detailed notes on the steps I've taken when I analyze data. It can be time consuming and maddening, when you are redoing work after finding a mistake, but it is often those same notes that help you find the mistake.

It would the REALLY helpful is Access would allow notes mode in SQL statements so users could notate their code. The ability to do this in MySQL workbench, along with the ability to execute highlighted code only, may cause me to eventually bail on Access, but I do really like the integration with Excel and even Word. I should explore more, but there is such an intellectual investment that has to be made to get comfortable with MySQL, I'm not sure I'll make that leap.

  © Blogger template 'A Click Apart' by 2008

Back to TOP