Access is designed as a rapid application development (RAD) tool that does not require programming. You can write and execute SQL statements in Access, but you have to use a back-door method to do it. To open a basic editor where you can enter SQL code, follow these steps:

Open your database and click the CREATE tab.

This will display the ribbon across the top of the window.

Click Query Design in the Queries section.

The Show Table dialog box appears.

Select the POWER table. Click the Add button and then click the Close button to close the dialog box.

Select the POWER table. Click the Add button and then click the Close button to close the dialog box.

A picture of the POWER table and its attributes appears in the upper part of the work area and a Query By Example (QBE) grid appears below it. Access expects you to enter a query now by using the QBE grid. (You could do that, sure, but it wouldn’t tell you anything about how to use SQL in the Access environment.)

Click the Home tab and then the View icon in the left corner of the Ribbon.

Click the Home tab and then the View icon in the left corner of the Ribbon.

A menu drops down, displaying the different views available to you in query mode.

One of those views is SQL View.

Click SQL View to display the SQL View Object tab.

Click SQL View to display the SQL View Object tab.

The SQL View Object tab has made the (very rational) assumption that you want to retrieve some information from the POWER table, so it has written the first part for you. It doesn’t know exactly what you want to retrieve, so it displays only the part it feels confident about.

Here’s what it’s written so far:

SELECT
FROM POWER ;

Fill in an asterisk (*) in the blank area in the first line and add a WHERE clause after the FROM line.

If you had already entered some data into the POWER table, you could make a retrieval with something like:

SELECT *
FROM POWER
 WHERE LastName = 'Marx' ;

Be sure the semicolon (;) is the last thing in the SQL statement. You need to move it down from just after POWER to the end of the next line down.

When you’re finished, click the floppy-diskette Save icon.

Access asks you for a name for the query you have just created.

Enter a name and then click OK.

Your statement is saved and can be executed as a query later.

About This Article

This article is from the book:

About the book author:

Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 40 books, including SQL For Dummies and Crystal Reports For Dummies. He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider.

This article can be found in the category: