Learn Access SQL
If you use Access to query data a lot, SQL for analysis is not a big step away. This is because you are already familiar with the logic of joining tables and manipulating data. Here are five things I did that enabled me to become proficient at writing complex SQL routines.
1. Force yourself to use SQL view (instead of design view) when writing queries–if you do not make yourself use it you won’t develop expertise. It is slower at first, but stick with it. Even for small tasks I was tempted to just quickly do it using design view–but I forced myself to use SQL view EVERY TIME.
2. If you get stuck, write a query in design view then change to SQL view as shown below (this is a bit like using Excel’s macro recorder to find out how to script an action in VBA).
3. The last two steps will get you on your way, but if you want to accelerate learning then buy and skim-read SQL Joes 2 Pros by Rick Morelan for a straightforward introduction to how SQL works and what it is. His books focus on SQL Server and they cover all elements of SQL (not just query writing) but they are great. Volume 1 is for the absolute beginner but a great summary, volume 2 goes a bit deeper into queries.
4. While writing queries in SQL mode use Access’ help file by typing a keyword (such as ‘CREATE’ or ‘SELECT’ for example) then place the cursor on the word and hit F1. This will take you to an explanation of the statement’s syntax and uses. You can also just print off every statement in their helpfile then study each one to get an idea of the language’s scope.
5. Use an online SQL formatter. Once you start writing lengthy SQL statements it can become unwieldy to keep track of the brackets and joins (if you end up using a lot of nested joins). Free online formatter tools (example here) automatically clean up your SQL statements allowing you to more easily spot errors in syntax. Sometimes they even highlight places where syntax inconsistencies are occurring which can help to ‘debug’ your syntax.
Once you can write simple select statement queries, you will start branching out into complex joins and update statements as well as INSERT, SELECT INTO, DROP, INDEX and other statements. At least that is how it worked for me.
Why use Access: getting past Excel
First off, if you like modelling data in Excel but do not use Access, branch into Access. It has vast advantages over Excel in some ways; one of its best advantages is being able to apply more structure to data-intensive modelling solutions, adding rigour to the calculation process. I once worked with a guy from finance who was pretty decent with Excel, but after spending a couple hours with me he was able to reduce the time he was investing in his month end process by a factor of about 20! Admittedly he was doing calcs in Excel’s ‘manual calculation’ mode and leaving them overnight since they took so long, but Access handled the same process (from a pure calculation perspective) in about 30 seconds. The queries we created also added rigor to the process and eliminated human error risks.
The beauty of Excel and Access is that they are commercially ubiquitous. Having moved across four different industries and working with several large-cap companies I am always amazed at how heavily used Excel is. Access less so, but this is just because people don’t know how to use it as much. It is generally available to staff in companies as standard MS Office suite software.
Why use SQL instead of Access’ design view
Some benefits of switching from design view to SQL view (when query writing) are:
- Enhanced querying functionality like using complex joins, union queries, nested queries, or correlated sub-queries
- Using SQL allows you to get an audit log of what you are doing–so you can email a colleague the table names used as well as the SQL in your query to communicate exactly what you did
- Once you have mastered the SQL language you can then write VBA scripts that consist of multiple SQL operations being run in sequence
- If you can write SQL you can write VBA code in Excel to retrieve Access data and return to Excel, which can be useful
- Once you are able to use Jet SQL in Access, it is easy to apply these skills to SQL Server, SAS’s PROC SQL, and other database or analytics software applications
As always, comments and suggestions welcome below. Thanks!