Let me tell you a story …
In 2002, I was interviewing for a database support position and feeling pretty good about my chances. I’d been working with Microsoft Access for a few years, everyone at the last job knew me as the expert and general tech guru and I was hoping things panned out because my jump from that company didn’t work out as planned and I needed a paycheck.
I wasn’t just about Access, either. I’d had my first database experience about 10 years earlier when my employer’s Alpha Four database had crashed – hard. With little knowledge, I’d been able to piece it together from backups. I’d designed important database apps for other users with Borland Paradox and then Microsoft Access. I’d easily saved my previous company thousands of dollars in software purchases and I found out years later that a couple of the apps I’d designed were still around.
Then the interviewer sat me down in a conference room and gave me a SQL exercise to do. I froze. The truth was that I hadn’t bothered learning SQL.
For all the work that I had done with various database programs, I had always relied on the interface query builders and had never taken the step of learning this important language. (To be fair, the SQL interface in Access is truly awful but, still …) Concepts like data normalization clicked with me immediately and I’d converted all my early Access macros over to VBA but it was just so easy to let the Query-by-Example (QBE) window write the SQL for me.
Needless to say, they found someone else for that job.
Reason 1: You don’t really understand databases until you know some SQL.
Structured Query Language (SQL) is the primary language for working with the relational databases that hold so much of our data. It doesn’t matter how well you’ve gotten by with the QBE interface in Access or whatever other software you’ve been using. If you can’t write a simple SQL statement to join two database tables and pull the necessary rows based on a couple of criteria then you are unable to effectively work with the data beyond the ways in which that interface allows you to. This leaves you completely dependent on the interface and how it implements basic concepts like sorting, grouping and selection of groups based on criteria.
Reason 2: You don’t fully understand the data either because, without SQL, you can’t analyze it as effectively.
Imagine you’re sending a text message to a friend and you want to say something like this:
“Do you want to go out to dinner tonight, maybe around 6? We can try that new place you’ve been talking about. We can take my car but we’ll have to stop for gas or we could take your car instead.”
Now imagine that, instead of an on-screen keyboard or voice rec, you just have icons to represent what you want to say. You have images of a plate and silverware, something that looks like a restaurant or a dining table, a car, a gas pump and a clock. You also have all the other standard emojis and such.
You might be able to get the point across about dinner at 6 but all the nuance is lost. The bit about which car to take is unlikely if not impossible so you’ll have to wait until your friend calls you or you see each other to work things out. In any case, it’s frustrating.
This is how using a query interface like the one in Access compares to actually being able to write the SQL code for yourself. The idea of writing code might sound more difficult but SQL is an easy enough language to learn that, with some experience, you could dash off the query as easily as you’d write the invitation that I used in the above example. With a query builder, you have to break down what you want to do and make it fit into the graphical interface that the software has provided, if you even can. In fact, the Access QBE window cannot handle UNION queries or table definition queries. It switches into SQL mode for those.
As another example, the screenshot below shows the solution to an assignment I gave my students when I was teaching SQL. It’s based on the AdventureWorks sample database and the challenge was as follows:
“Get percentage of quantity rejected on purchase orders by vendor. Use CASE statements to fill in explanations.”
The single SQL statement at the top provides a complete analysis of vendor performance with percentages and a CASE statement to explain the numerical credit ratings. The results at the bottom are report-ready. Anyone with even a basic understanding of queries in Access should immediately be able to work out the SQL code at the top even if they’ve been relying on the QBE window. With a little experience, it’s readable at a glance. SQL Server Management Studio (or any decent code editor) allows for the nice formatting and automatic color-coding you see here.
Try doing that as efficiently in an interface like this …
Reason 3: Without SQL, you don’t know what you don’t know.
Graphical query interfaces are designed to provide the functions needed in maybe 90% of the queries that the users will need to write but they are inherently limiting. They encourage relatively simple queries that can be easily plotted out inside the window or the use of multiple queries that can reference each other but SQL is capable of so much more. Users who rely on these graphical tools will never see a good deal of what SQL can do and can easily miss out on functionality that would otherwise enhance their applications or open them up to new possibilities.
Worse, so much in SQL is simply impossible in a query environment like the one in Microsoft Access. It’s finicky behavior and idiosyncratic SQL syntax actually discourages the user from making direct SQL changes and its SQL display mode is so ugly and hard to read that the user is almost browbeat into surrendering to the authority of the QBE interface which is still unpleasant to use for anything more than simple queries. Also, forget about optimizing your queries for the best performance so your database doesn’t slow down.
Reason 4: Extended SQL – Scripts, Stored Procedures, Triggers and more …
Single queries are great but sometimes you need multiple queries to get a job done like when you need to add an employee record and supporting records in other tables. Stored procedures are great for that but you need to have a working understanding of SQL and whatever extensions to it the database software provides. Stored procs and SQL scripts also enable you to declare variables, perform looping operations and carry out other programming tasks close to the database so that data doesn’t have to travel between the database and an external program. The Access QBE and other query interfaces don’t support any of this.
Would you like to track changes to tables, log who changed specific records and when? Triggers, small amounts of code that runs every time a table is changed, are great for that.
Temporary tables in memory are useful for complex query operations that need more than one query. A graphical query interface might allow for subqueries but they can be very slow. Temporary tables are much faster.
All of these things are available to you through the SQL language, an incredibly powerful tool that will give you very precise control over your data
Reason 5: SQL is a great first programming language and it’s not going away.
Technically the basic SQL that you use to manipulate data in tables is not a programming language but, once you get into the extensions of the language like T-SQL (SQL Server) or PL-SQL (Oracle), you have access to variables, flow control statements (IF … THEN statements and FOR loops) and error handling that most definitely does qualify as programming. It’s a great introduction to procedural programming and an excellent addition to your resume.
SQL has been around since 1970 and the relational databases it’s used to manage will remain a popular way to store various types of data for many years to come. A comfortable knowledge of SQL will always be useful whether you stick with desktop databases like Access or move on to more advanced options.
Plus, the basics are easy to learn and you can gain experience over time so there’s really no reason not to take some time and get comfortable with it.
In my program, I used Ben Forta’s Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes as a guide and my students found it useful and easy to understand. It’s a great reference book that you can easily look back on as needed.
Finally, if you find videos a helpful way to learn, WiseOwl has a very good YouTube series that will take you from the basic queries through advanced SQL.
Do you need help with Microsoft Access?
Comeau Software Solutions specializes in the development and enhancement of Microsoft Access applications. We can also rescue Access projects that have gone off-track and provide assistance when it's time to move to another solution. Please contact us for more information on how we can help you with your database needs.
Also, check out our online course Managing Your Data with Microsoft Access. You can learn how to build and manage your own applications and work with your data more efficiently!