6 ms·

Two Spreadsheets, Microsoft Access, and a Pulitzer Prize for Public Service

64 points by agronaut 10y ago

lmg643 10y ago
Great article. Microsoft Access was my first introduction to databases. (For those who don't know, MS Access is like "excel for databases.") Gave me a good-enough sense of what was happening under the hood. Started me on a career in software. 15 years later, I'm in a recently updated version of enterprise SQL Server (say what you will, but it works for us). I chuckle every time i get to write IIF(x,y,z) in a SELECT statement. I was doing that in access in 2003.
- mirimir 10y ago
  Excel and Access are amazingly good products. The Jet SQL syntax is a little strange, but Access will handle substantial datasets. You can workaround the table size limit using views. It won't be fast, but it will work.
  - ianamartin 10y ago
    I'm not so sure I agree about access, but I totally agree about excel. I mostly use Mac and but I will always keep a Windows box around if only to run excel. Sometimes that's the tool you need for the job, and no other will work as quickly or as well. The windows excel team knocks it out of the park pretty much every time.
    - mirimir 10y ago
      I love mainly two things about Excel. One is calculation on multiple cores. The other is grace in handling huge files. I've worked with 100MB files in Excel. If I turn off auto calculation, that's actually workable. Linux spreadsheet apps tend to fall over and die when files get too big, or there are too many formulas.
      - Vexs 10y ago
        Libreoffice calc is the only bit of libre software I've ever found inferior to the non-libre version. Excel is just that good, and most people use it as "Microsoft Grid Paper 2016"
    - kawera 10y ago
      Excel for Mac is excellent too. Any particular reason for using the Windows version?
      - jpau 10y ago
        Excel for Mac is excellent, compared to the wider market. Unfortunately it is both missing many features and runs much slower than its Windows equivalent. For the work I do (as a financial analyst), Excel for Mac is unworkable. See Microsoft's side-by-side comparison - https://support.office.com/en-us/article/Compare-Excel-2016-for-Mac-with-Excel-2013-for-Windows-1b24f293-739c-4120-b884-893b6687e0d0 https://support.office.com/en-us/article/Compare-Excel-2016-...
        mirimir 10y ago
        Are you using 2016, which seems to be single-threaded?
        rtpg 10y ago
        There's no way to customise shortcuts in the Mac version? Huh
    - p_l 10y ago
      The real big power of Access was that it could use other database engines through ODBC. Quick RAD tool for databases!
      - glaberficken 10y ago
        That's exactly how I use Access at work. We host all the data on MS SQL Server that is managed by the IT department (scheduled imports, maintenance etc) and then everyone links to the tables from Access where they create queries with the visual wizard. these can then be linked to from Excel to produce reports where a layman can just do data refresh to update. Makes for a really flexible data reporting workflow that fits the knowledge level of each user. SQL Server (Data Tables and Views) >>> Access (Queries via visual wizard) >>> Excel (Reports linked via MS Query) PS: We also use data cubes (SQL Analysis Services) but those have to be setup on the IT side, so we tend to disfavour them due to depending on a bottleneck human resource. Although with Cubes you can write Cube formulas straight from excel bypassing the Access stpe
  - mkane848 10y ago
    Access Web Apps, on the other hand, are a nightmare. So many stripped features compared to what the Desktop version can do, it was incredibly frustrating to use.
- m_mueller 10y ago
  Imagine what Microsoft could have made out of Access. It really should have been extended to a full blown multi-user server/client DB system, keeping the RAD capabilities. It would have really rocked as a replacement for all the Lotus Notes databases (that are still around). Instead we got SharePoint :(
  - p_l 10y ago
    It already was such a system - configure the database to use a named ODBC data source that points to shared server instead of JET file, distribute, run.
  - dan_b 10y ago
    I started off with an Access database. Then we split the JET DB backend out from the front end and put that on a shared drive. Then we replaced the backend with SQL Server (without changing the front end.) Then eventually it all got migrated to an ASP.Net backend + single page application. It's still running 8 years later. Access is an incredibly flexible tool.
cypherpunks01 10y ago
Nice! SQL is good like that, in my experience I've found that non-devs are able to understand and use it somewhat surprisingly quickly. Probably has something to do with the clause structure & parsing being fairly similar to that of a natural language sentence.
fiatjaf 10y ago
This article shows why you shouldn't trust journalists.
Vexs 10y ago
Excel is one of the best, and most misused tools in the world. I know one, maybe two people besides myself that do anything beyond using it as grid paper or to render graphs, and that's a shame. The MS office classes around here, from what I know, don't even mention functions beyond adding numbers. Hell, Excel was basically the first thing I ever programmed in. Just playing with it to see what I could make it do. Nowadays I see people spending an hour making a python script to parse a file, when I can just drop it in excel use a couple functions and maybe a macro, and have it parsed in 10 minutes. It's a real shame. I wouldn't be surprised if a lot of things in companies could get a lot better if everyone learned excel overnight. Some people's job basically amounts to doing something a spreadsheet and a little macroing can do.
- throwaway049 10y ago
  It generally takes me much longer to parse data in Python (I use Pandas sometimes) than in Excel/Access but I like to think that's because I've put much more practice in the latter and have to spend more time reading the docs for the former.
  - Vexs 10y ago
    Plus with standard scripting languages you have to do a lot of backbone work too- load the file, parse the file, etc before you can ever get into doing any sort of computations. Plus excel is vastly easier to debug, thanks to it all being live.
vandinem 10y ago
Good article, but the spreadsheet/database skills did not seem to be the real story. Yes, very useful, and powerful, but LOTS of people have developed those skills. Instead, I found myself asking, "Where did the data come from?" He asked for it. Who did he ask? The power of the story seemed to me to be that he asked a powerful question, figured out how to get the data to answer that question, and then figured out what skills he'd need to sift the data.

64 points by agronaut 10y ago

Great article. Microsoft Access was my first introduction to databases. (For those who don't know, MS Access is like "excel for databases.") Gave me a good-enough sense of what was happening under the hood. Started me on a career in software. 15 years later, I'm in a recently updated version of enterprise SQL Server (say what you will, but it works for us). I chuckle every time i get to write IIF(x,y,z) in a SELECT statement. I was doing that in access in 2003.

mirimir 10y ago
Excel and Access are amazingly good products. The Jet SQL syntax is a little strange, but Access will handle substantial datasets. You can workaround the table size limit using views. It won't be fast, but it will work.
- ianamartin 10y ago
  I'm not so sure I agree about access, but I totally agree about excel. I mostly use Mac and but I will always keep a Windows box around if only to run excel. Sometimes that's the tool you need for the job, and no other will work as quickly or as well. The windows excel team knocks it out of the park pretty much every time.
  - mirimir 10y ago
    I love mainly two things about Excel. One is calculation on multiple cores. The other is grace in handling huge files. I've worked with 100MB files in Excel. If I turn off auto calculation, that's actually workable. Linux spreadsheet apps tend to fall over and die when files get too big, or there are too many formulas.
    - Vexs 10y ago
      Libreoffice calc is the only bit of libre software I've ever found inferior to the non-libre version. Excel is just that good, and most people use it as "Microsoft Grid Paper 2016"
  - kawera 10y ago
    Excel for Mac is excellent too. Any particular reason for using the Windows version?
    - jpau 10y ago
      Excel for Mac is excellent, compared to the wider market. Unfortunately it is both missing many features and runs much slower than its Windows equivalent. For the work I do (as a financial analyst), Excel for Mac is unworkable. See Microsoft's side-by-side comparison - https://support.office.com/en-us/article/Compare-Excel-2016-for-Mac-with-Excel-2013-for-Windows-1b24f293-739c-4120-b884-893b6687e0d0 https://support.office.com/en-us/article/Compare-Excel-2016-...
      - mirimir 10y ago
        Are you using 2016, which seems to be single-threaded?
      - rtpg 10y ago
        There's no way to customise shortcuts in the Mac version? Huh
  - p_l 10y ago
    The real big power of Access was that it could use other database engines through ODBC. Quick RAD tool for databases!
    - glaberficken 10y ago
      That's exactly how I use Access at work. We host all the data on MS SQL Server that is managed by the IT department (scheduled imports, maintenance etc) and then everyone links to the tables from Access where they create queries with the visual wizard. these can then be linked to from Excel to produce reports where a layman can just do data refresh to update. Makes for a really flexible data reporting workflow that fits the knowledge level of each user. SQL Server (Data Tables and Views) >>> Access (Queries via visual wizard) >>> Excel (Reports linked via MS Query) PS: We also use data cubes (SQL Analysis Services) but those have to be setup on the IT side, so we tend to disfavour them due to depending on a bottleneck human resource. Although with Cubes you can write Cube formulas straight from excel bypassing the Access stpe
- mkane848 10y ago
  Access Web Apps, on the other hand, are a nightmare. So many stripped features compared to what the Desktop version can do, it was incredibly frustrating to use.
m_mueller 10y ago
Imagine what Microsoft could have made out of Access. It really should have been extended to a full blown multi-user server/client DB system, keeping the RAD capabilities. It would have really rocked as a replacement for all the Lotus Notes databases (that are still around). Instead we got SharePoint :(
- p_l 10y ago
  It already was such a system - configure the database to use a named ODBC data source that points to shared server instead of JET file, distribute, run.
- dan_b 10y ago
  I started off with an Access database. Then we split the JET DB backend out from the front end and put that on a shared drive. Then we replaced the backend with SQL Server (without changing the front end.) Then eventually it all got migrated to an ASP.Net backend + single page application. It's still running 8 years later. Access is an incredibly flexible tool.

cypherpunks01 10y ago

Nice! SQL is good like that, in my experience I've found that non-devs are able to understand and use it somewhat surprisingly quickly. Probably has something to do with the clause structure & parsing being fairly similar to that of a natural language sentence.

fiatjaf 10y ago

This article shows why you shouldn't trust journalists.

Vexs 10y ago

Excel is one of the best, and most misused tools in the world. I know one, maybe two people besides myself that do anything beyond using it as grid paper or to render graphs, and that's a shame. The MS office classes around here, from what I know, don't even mention functions beyond adding numbers. Hell, Excel was basically the first thing I ever programmed in. Just playing with it to see what I could make it do. Nowadays I see people spending an hour making a python script to parse a file, when I can just drop it in excel use a couple functions and maybe a macro, and have it parsed in 10 minutes. It's a real shame. I wouldn't be surprised if a lot of things in companies could get a lot better if everyone learned excel overnight. Some people's job basically amounts to doing something a spreadsheet and a little macroing can do.

throwaway049 10y ago
It generally takes me much longer to parse data in Python (I use Pandas sometimes) than in Excel/Access but I like to think that's because I've put much more practice in the latter and have to spend more time reading the docs for the former.
- Vexs 10y ago
  Plus with standard scripting languages you have to do a lot of backbone work too- load the file, parse the file, etc before you can ever get into doing any sort of computations. Plus excel is vastly easier to debug, thanks to it all being live.

vandinem 10y ago

Good article, but the spreadsheet/database skills did not seem to be the real story. Yes, very useful, and powerful, but LOTS of people have developed those skills. Instead, I found myself asking, "Where did the data come from?" He asked for it. Who did he ask? The power of the story seemed to me to be that he asked a powerful question, figured out how to get the data to answer that question, and then figured out what skills he'd need to sift the data.