How To Write SQL Queries In Access

How To Write SQL Queries In Access

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.

how to write sql queries

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:

how to write SQL queries

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:

how to write sql queries in accessNotice 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.

how to write sql queriesThe 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.

This entry was posted in Database Theory, MS Access, Queries, Utilities and tagged , , , , . Bookmark the permalink.

3 Responses to How To Write SQL Queries In Access

  1. Ben says:

    Hi Charlie,The answer is simply a ‘no’ – Access will not remember spaces or line breaks as it’s not intended to be edited this way.
    You will need to provide an Access form which will load the SQL statement (probably from a table storing your SQL) and then you can use VBA code to control the layout if users are going to interact with it.
    I guess this is why SQL is quite relaxed with conventions and focuses more on the commands not the presentation.
    If you need any help with the form interface approach, I could probably create a utility for you (for a fee) – let me know.Ben

  2. Charlie Post says:

    Hi,
    Is there any way to keep the spacing you put into your SQL? I would like another user to be able to easily change a few items, which is much easier to do if I can space the text out in the SQL. However, it seems the default in Access 2010 is to just go back to jamming all of the text together.
    EXAMPLE: Here is how Access likes to show it:
    WHERE (((degrees.MAJORNUM)=1) AND ((degrees.CIPCODE) Like “52.*”) AND ((geoinfo.state)=”Ohio”) AND ((geoinfo.County) Like “Cuy*”))

    I would like it to display like this:
    WHERE (
    ((degrees.MAJORNUM)=1)
    AND
    ((degrees.CIPCODE) Like “52.*”)
    AND
    ((geoinfo.state)=”Ohio”)
    AND
    ((geoinfo.County) Like “Cuy*”)
    )

    Thanks for any help you can provide.
    Charlie

  3. Ben Beitler says:

    Some of the conventions used in Access SQL can be left out as there are optional. This includes the very last character – the semi-colon (;). Also where I abbreviated the table name (Customers As C), I could have also left out the word ‘As’ – it’s pretty friendly!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.