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.
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.
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.
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.
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"
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.
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
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.
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 :(
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.
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.
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.
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.
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.
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.
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.
21 comments
[ 2.0 ms ] story [ 53.0 ms ] threadSometimes 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.
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-...
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
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.