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
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!
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:
((degrees.CIPCODE) Like “52.*”)
((geoinfo.County) Like “Cuy*”)
Thanks for any help you can provide.
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