Access Select Queries 2003

Overview

Queries are the key to using Access effectively. All forms and reports should be based on queries.

Queries are based on underlying tables. There several different types of queries, but this guide will only cover Select Queries. Select Queries will not change the data in the original table.

Creating a New Select Query Open the address.mdb database. Click on the Tables tab and open Address Table and look at its fields. Close the table. Click on the Queries tab and then double click on Create Query in Design View. A list of all the tables in the database will appear. Click on the Address Table. Click the Add button and then the Close button.
Adding Fields to a Query To select individual fields, click on the field you want, hold the mouse down and drag it to the first blank Field line in the grid at the bottom of the screen. You may drag them down in the order you want them to appear in the query.
To drag all the fields, double click on the Address Table header. Click once on any of the highlighted fields, hold the mouse down and drag it to the first blank Field line in the grid below.
§ To move a field to different location, click on the gray header of the field you want to move. Put your cursor back on the header until a white arrow appears. Click and hold the mouse down. A small nearly invisible box will appear below the arrow. Without letting up on the mouse, drag the field where you want it. You will not actually be changing the order in the table, just the order that the query presents the information. · To delete a field, click on the small gray header of the field in the grid. When you see a small black arrow ¯, click. The field will be highlighted. Hit the Delete key.
Running a Query
· Click on the Run icon	or click on the Datasheet View icon
Returning to the Design Grid Click on the Design View icon
Saving a Query · Click the floppy disk icon (or File and Save). It is a good idea to always put Query on the end of the name
Sorting · Click once in the Sort line under Last. · Click the drop down arrow and select Ascending. Run the query and notice that the last names are in alphabetical order.
· Return to Design View. · Click once in the Sort box under First · Click the drop down arrow and select Ascending. Run the query and notice that the last and first names are in alphabetical order.
· Fields are sorted left to right. You can move the fields to get the correct sort. You can also add the field twice and uncheck the Show box so the second field will not display.
Using Criteria You can limit the amount of information that a query actually displays by adding criteria to the query. · Open Address Query in Design View. · Type smith in the top Criteria line under Last. Run the query. This displays only records that contain a last name of Smith. You do not need to put the quotation marks around the word; Access will do that for you.
		· Type sally in the top Criteria line under first. Run the query. This displays only records that contain a last name of Smith and first name of Sally.
		· Remove sally from the top Criteria line under first and add it to the second line. Run the query. This displays records that contain a last name of Smith or the first name of Sally.

Criteria Operators
Operator	Description	Example	Result
>	Greater than	>50	Value exceeds 50
>=	Equal to or greater than	>=50	Value is 50 or greater
<	Less than	<50	Value is less than 50
<=	Equal to or less than	<=50	Value is 50 or less
<>	Not equal	<>50	Value is any number but 50
Between And	Value between two numbers or dates	Between #2/2/95# And #2/5/95#	Dates between 2/2/95 and 2/5/95
In	Finds multiple criteria	In(KS,IA)	Value equals KS or IA
Null	Finds blank records	Null	No value in this record
Is not null	Finds non-blank records	Is not null	Any value in record
Like	Finds matching records	Like "38"	Value equals 38
Not	Finds everything except	Not "38"	All values except 38
?	Replaces single character	"wom?n"	Finds women or woman
*	Replaces all	Like "38*"	Finds all that start with 38
		Like "*38*"	Find all with 38 in the record
		Like "*38"	Find all that end in 38

Selecting Null & Not Null

• Open Address Query in Design View.
• Type null in the Criteria line under the Phone field. Run the query. This displays only the records without a phone number.
• Return to Design View.
• Type not null in the Criteria line under the Phone field. Run the query. This displays only the records that have a phone number.

Selecting Specific Information

• Open Address Query in Design View. Add 543* to the Criteria line under Phone. Run the query. This displays only those phone numbers that begin with 543.
• Open Address Query in Design View. Add S* to the Criteria line under First. Run the query. This displays only those first names that begin with S.
• Open Address Query in Design View. Add *1 to the Criteria line under Phone. Run the query. This displays only those phone numbers that end with 1.
• Open Address Query in Design View. Add >"67*" to the Criteria line under Zip. Run the query. This displays only those zip codes greater than 67.

Selecting Exact Information in a Pop Up Window

• Open Address Query in Design View. Add a [Enter a Zip Code] to the Criteria line under Zip. Run the query. Enter 67204. This displays only zip codes that match exactly. If you enter a zip code that is not in the database, the data view will come up empty.

RLS

10/06/2005