Just in case you haven’t heard, SQL stands for Structure Query Language and is the backbone code to asking those all important questions in your Access database or any database application as a matter of fact.

This post will introduce you in how to write SQL queries and why in some cases this is the only option open to you but taking advanatge of both the QBE grid (another acronym for Query By Example) and SQL View screens will get you started quickly and easily.

How To Write SQL Queries In Access – Some Simple Steps

If as with like most Access databases you have related tables or joins between tables the likelihood will be to bring two or more tables or queries together in a query and apply various criteria, calculations and sorting to output and answer your request.

Therefore, this example is a typical one where two tables with a ‘many-to-one’ relationship is established between the Customers and Orders tables.

Using the QBE grid one does not need to know how to write SQL queries as this grid provides for 95% of most user requirements but at some stage for the more advanced user they may just need to use the SQL generated elsewhere in for example VBA code and other aspects of this powerful application. The same relationship defaults to the following SQL:

SELECT
FROM Customers INNER JOIN Orders ON
Customers.[Customer ID] = Orders.[Customer ID];

As you add more to the query by choosing the fields, creating calculations and sorting so the SQL builds and doesn’t always make easy reading as the example below shows:

SELECT Customers.[Company Name], Customers.Country,
Orders.[Order ID], Orders.[Order Date], Orders.[Required Date],
Orders.[Shipped Date], Orders.[Order Amount], Orders.Freight
FROM Customers INNER JOIN Orders ON Customers.[Customer ID]
= Orders.[Customer ID]
WHERE (((Customers.Country)="uk") AND ((Year([Order Date]))=2008))
ORDER BY Customers.Country, Orders.[Order Date];

If the intention is to work with SQL for other areas of Microsoft Access (and beyond) then some standards should be adapted and applied which may require switching views and tidying up your SQL by learning how to write SQL queries.

1. Start by using the QBE grid to create the two tables showing the correct join and then switch to SQL view mode.

2. I then separate out the keywords making them in uppercase for clarity (SQL is not case sensitive) and have the following canvas as follows:

3. The join between the two tables is already in place (and once you know a little more about SQL, you can write this here yourself). With this join I abbreviate the two table names Customers and Orders to a ‘C’ and an ‘O’ by adding this element after each table name and edit this line as follows:

Customers C INNER JOIN Orders O ON C.[Customer ID] = O.[Customer ID]

(Look at the illustration above and compare the two).

4. Now I can add my fields, set criteria and apply sorting making sure I take advantage of first using the abbreviated table reference preceding each field call and spacing out my text so it’s easy to read as an example below:

Notice each field on a new line starts with a comma at the beginning making it easy to read too as spaces and line returns are also ignored.

5. Save your query and run. Now take a look at the QBE grid design view and see what happened here too.

The SQL view is simply a free text editor and can be copied into VBA or other areas of Access going forward. Of course there are many SQL commands you may not be familiar with but don’t worry there’s a free eBook for you on the subject which is a generic reference on how to write SQL queries.

Tags: Access, building queries with sql, creating sql queries in access, how to write sql queries, sql syntax language, working with access sql