How to Write an Access Query
Although writing a query in Microsoft Access can be done with traditional hand-written SQL statements, Access makes query creation more intuitive by providing a visual grid in which you can select your query's fields and criteria. With this article you will use that grid to make two specific types of select queries. You can then apply this experience to create queries tailored to your needs.
Other People Are Reading
Instructions
-
-
1
Create some sample data. Open Access, select "File > New," then choose "Blank Database." Enter a name and filename for the database if your version of Access prompts you for these, and then click "Create."
-
2
Click the drop-down arrow next to the text "Click to Add" if you're using Access 2007. Choose "Text" and type "cdtitle" to create the first field of a music table.
If you are using Access 2003 or earlier, create a table by pressing "F11," clicking "Objects > Tables," and then clicking "New," on the toolbar. Double-click "Datasheet View," then double-click "Field1" and rename it as "cdtitle."
-
-
3
Create five more fields in the same manner as the first. Use the following types and field names, but do not enter the quotation marks or "|" characters. (You do not have to enter the data types if your version of Access does not prompt you for them.)
number: "qtysold" | date and time: "datesold" | currency: "unitprice" | text: "buyercity" | text: "buyerstate"
-
4
Press "CTRL + S" to save the database and table. Type "music" for the table's name if Access asks you for it.
-
5
Enter the following data in the music table, in the order in which you created the fields in Steps 1 and 2:
beethoven symphony 5,3,12/14/2009,$4.97,moorcroft,wyoming
beethoven symphony 5,4,11/30/2009,$4.97,amityville,new york
vaughan williams fantasia,6,12/7/2009,$8.75,chicago,illinois
vaughan williams fantasia,2,12/25/2009,$8.98,san francisco,california -
6
Create your first query. Click "Create > Query Design" from the Access toolbar in Access 2007. Double-click the "music" item in the "Show Table" dialog box, and then "Close" to close the dialog box.
Click "Objects > Queries" and then "New" on the toolbar to create a query in Access 2003 or earlier. Click "Design View" and then "OK" in the "New Query" dialog box.
-
7
Drag the "*" character from the small "music" window sitting above the query grid to anywhere on the grid. Run the query by pressing the toolbar's "Run" icon (with the exclamation mark next to it) if you're using Access 2007. Press your toolbar's "View" button for Access 2003 or earlier.
Compare the data in the resulting window with your original table, observing that it is the same.
-
8
Revise your query by first right-clicking the "Query1" tab and choosing "Design View."
-
9
Drag the field "cdtitle" from the "music" window onto the column next to the one containing the checked checkbox. Uncheck the checkbox of the new column to indicate you do not want to show this field. Type the following in the "Criteria" row:
Like 'b*'
-
10
Re-run the query and look at the resulting rows, noting that they contain only the cdtitles matching the criteria you entered. ("Like b*" matches "beethoven" but not "vaughan williams...")
-
11
Modify the query to display only certain fields. In the "Design View," click the top of the column containing the checkbox and press "Delete." Drag the fields "cdtitle" and "buyercity" to unoccupied columns in the query grid, then re-run the query to see results for just the fields you chose.
-
1
