Tuesday, April 13, 2021

Can you create a query in MS Access using a SQL Statement?

Microsoft Access is designed such that even if you do not know SQL or SQL syntax you could still use the application. As a consequence, there is no obvious starting point to write a SQL Statement and execute it. However, it does allow you to write a SQL Statement, save the query and execute it.

Open access application. I have just one simple table 'BOOK1'.

In the 'Queries' tab of Create click on Query Design to open a tabbed page 'Query 1" as shown.


Query 1 has two panes. The top one is empty and it is here you can write a SQL Statement and the bottom pane is where you can compose a query using visual elements.

Right-click in the empty top pane to show this drop-down.


Click on 'SQL View'. This view is not the same as 'View' in SQL Server. This opens a text editor where you can write a query. Note that it has just the 'SELECT'. It expects you to write a "SELECT" statement.


Note that there is a Table 1 called 'Book1' shown here.


Now I write a SQL query against this table.

SELECT ID, Field2 
From Book1

Here is the statement in the SQL View's editor pane.

Now save the query, Query1. You need to click on the tab to save as shown.


You can save it with a different name which is usually recommended. I saved it as BookQry as shown.




Now double click on BookQry on the left to open the query to show the result.






















No comments: