Create a simple select query

A select query is used to create subsets of data that you can use to answer specific questions. It can also be used to supply data to other database objects. Once you create a select query, you can use it whenever you need.

This topic explains how to create a simple select query that searches the data in a single table. You will also learn how to enhance the query by defining record criteria and adding calculated values.

In this article


Introduction

A select query is a type of database object that shows information in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). A query can get its data from one or more tables, from existing queries, or from a combination of the two. The tables or queries from which a query gets its data are referred to as its recordsource.

Whether you create simple select queries by using a wizard or by working in Design view, the steps are essentially the same. You choose the recordsource that you want to use and the fields that you want to include in the query — and, optionally, you specify criteria to refine the results.

After you have created a select query, you run it to see the results. Running a select query is simple — you just open it in Datasheet view. You can then reuse it whenever you need, for example, as a recordsource for a form, report, or another query.

 Note   For more information about using a query as a recordsource for a form or report, see the article Use a query as a recordsource for a form or report.

There are several types of query, each serving a different purpose. For example, a select query displays data. An action query changes the data in its datasource, or creates a new table. A parameter query prompts you to supply criteria when you run it. This topic only covers select queries.

 Note   This topic describes how to create select queries that return data from a single table. If you want to create queries that return data from two or more tables, see the article Create a query based on multiple tables.

Top of Page Top of Page

Create a query

In this exercise, you will create the London Contacts select query. You can create this query either in Design view or by using a wizard. In addition, if you are familiar with writing SQL statements (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.), you can create a query while working in SQL view by writing a simple SELECT statement.

Before you begin, you need to have a table that contains data. Sample data is provided below in HTML table form.

CustomerID Company Address City StateOrProvince PostalCode CountryOrRegion Phone Contact BirthDate
1 Baldwin Museum of Science 1 Main St. New York NY 12345 USA (202) 555-0122 Steve Riley 03-Dec-45
2 Blue Yonder Airways 52 1st St. Boston MA 01234 USA (201) 555-0123 Waleed Heloo 21-Mar-59
3 Coho Winery 3122 75th Ave. S.W. Seattle WA 98100 USA (206) 555-0042 Guido Pica 01-Apr-73
4 Contoso Pharmaceuticals 1 Contoso Blvd. London NS1 EW2 UK (171) 555-0125 Zoltan Harmuth 16-Jun-67
5 Fourth Coffee London W1J 8QB UK (171) 555-0165 Julian Price 09-Aug-71
6 Consolidated Messenger 3123 75th St. S Seattle WA 98100 USA (206) 555-0007 Christine Hughes 27-May-48
7 Graphic Design Institute 151 Strand London WC2R 0ZA UK (171) 555-0178 Dana Birkby 12-Aug-61
8 Litware, Inc. 3 Macrofirm Parkway Portland OR 97200 USA (503) 555-0086 Jesper Aaberg 01-Sep-75
9 Tailspin Toys 22 Wicklow Street London WC1 0AC UK Phil Gibbins 15-Feb-53
10 Woodgrove Bank 37 Lothbury London EC2R 7ED UK (171) 555-0101 Tom Perham 25-Sep-38

You can either enter the data in this sample table manually, or you can copy this table to a spreadsheet program, such as Microsoft Office Excel 2007, and then import the resulting worksheet into a table in Microsoft Office Access 2007.

ShowEnter the sample data manually

  1. On the Create tab, in the Tables group, click Table.

Office Access 2007 adds a new, blank table to your database.

 Note   You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.

  1. Double-click the first cell in the header row and type the name of the field in the sample table.

By default, Access denotes blank fields in the header row with the text Add New Field, like so:

A new field in a datasheet

  1. Use the arrow keys to move to the next blank header cell and then type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.
  2. Enter the data in the sample table.

As you enter the data, Access infers a data type for each field. Each field in a table has a specific data type, such as Number, Text, or Date/Time. Data types help ensure accurate data entry and help to prevent mistakes, such as using a telephone number in a calculation. You can specify the data type for each field in a table, and can change a field's data type under certain circumstances. For this sample table, you should let Access infer the data type.

  1. When you finish entering the data, click Save

Keyboard shortcut  Press CTRL+S.

The Save As dialog box appears.

  1. In the Table Name box, enter Customers, and then click OK.

ShowCopy the table to a spreadsheet program and then import it into Access

  1. Start your spreadsheet program and create a new, blank file. If you use Excel, a new, blank workbook is created by default.
  2. Copy the sample table provided in the previous section and paste it into the first cell of the first worksheet.
  3. Using the technique provided by your spreadsheet program, name the worksheet Customers.
  4. Save the spreadsheet file to a convenient location and go to the next steps.
Import the table into Access
  1. In a new or existing database:

On the External Data tab, in the Import group, click Excel.

-or-

Click More, and then select a spreadsheet program from the list.

The Get External Data - Program Name Spreadsheet dialog box appears.

  1. Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.

The Import Spreadsheet Wizard starts.

  1. By default, the wizard selects the first worksheet in the workbook (Customers, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the wizard page. Click Next.
  2. On the next page of the wizard, select First row contains column headings, and then click Next.
  3. The next page of the wizard offers you an opportunity to change field names and data types or to omit fields from the import operation, by using the text boxes and lists under Field Options. You should not do this for this example. Click Next.
  4. On the next page of the wizard, in the Field Options box, select Yes (No Duplicates) from the Indexed list, and select Long Integer from the Data Type list. Click Next to continue.
  5. On the next page of the wizard, select the Choose my own primary key option, choose CustomerID from the list, and then click Next.
  6. By default, Access applies the name of the worksheet to your new table. Make sure that the table is named Customers, and then click Finish.
  7. On the last page of the wizard, you have the option of saving the import steps for later reuse. Because you are importing a sample table, it is not recommended that you save the import steps.

 Note   If you don't have a spreadsheet program, you can copy the sample data to a text editor, such as Notepad. For more information about importing text data, see the article Import or link to data in a text file.


Build the query

First, you will use a wizard to create the query, based on the sample table that you just created. Then, you will enhance the query in Design view. At each step, you can review the SQL statement that is automatically generated by the steps that you take.

  1. On the Create tab, in the Other group, click Query Wizard.
  1. In the New Query dialog box, click Simple Query Wizard, and then click OK.
  2. Under Tables/Queries, click the table that has the data that you want to use. In this case, click Table: Customers. Note that a query can also use another query as a recordsource.
  3. Under Available Fields, double-click the Contact, Address, Phone, and City fields. This adds them to the Selected Fields list. When you have added all four fields, click Next.
  4. Name the query London Contacts, and then click Finish.

Access displays all of the contact records in Datasheet view. The results show all of the records, but show only the four fields that you specified in the query wizard.

ShowReview the initial SQL statement

Click SQL View on the Access status bar; or, right-click the query object tab, and then click SQL View. Access opens the query in SQL view, and displays the following:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City]
FROM Customers;

As you can see, in SQL the query has two basic parts: the SELECT clause, which lists the fields that are included in the query, and the FROM clause, which lists the tables that contain those fields.

 Note   If you added the fields in a different order from the preceding procedure, the order that you used will be reflected in the SELECT clause.


  1. Close the query, and note that your query is automatically saved.

Top of Page Top of Page

Add criteria to the query

To restrict the records that are returned in the query results, you can specify one or more criteria.

You can think of a query criterion as a condition that you specify for a field. The criterion specifies a condition, based on field values, that expresses what you want to include in the query, such as "show only those records where the value of City is London".

Open the query in Design view. You will add a criterion to the City field so that you see only those contacts who are from London in the query results. You will also add criteria to the Address field and the Phone field, to further refine your query results.

  1. In the Criteria row of the City field, type london.

Now, add two more criteria to make the results even more meaningful. Suppose that you only want to see the records in which both the address and the phone number are present.

  1. In the Criteria row of the Address field, type Is Not Null AND <>"". Do the same in the Criteria row of the Phone field.

 Note   The criterion, Is Not Null AND <>"", is true whenever there is any known, non-empty value for the field for which it is a criterion. It is false whenever there is no known value (Null), or when the value is known to be empty (""). You can use this expression to check a field for a known, non-empty value.

  1. Switch to Datasheet view to see the results.

ShowReview the SQL statement with criteria

Click SQL View on the Access status bar, or right-click the query document tab and then click SQL View. Access displays the following SQL code:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City]
FROM Customers
WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"") 
 AND ((Customer.[Phone]) Is Not Null And (Customer.[Phone])<>"") 
 AND ((Customer.[City])="london"));

You will note that the SQL statement now has a WHERE clause. The criteria that you specify for query fields appear in SQL in the WHERE clause. In this case, they are combined by using the AND operator.


What if you don't want the conditions to get combined by using the AND operator? In other words, how do you specify two or more criteria but include the records that satisfy one or both of them?

Specify alternate criteria sets by using OR

Suppose you want to see all the records where City equals London and where at least one kind of contact information — either the address or the phone number — is available. You want to combine the criteria by using the OR operator, like this:

To specify alternate criteria, use both the Criteria and Or rows in the design grid. All records that meet the criteria defined either in the Criteria row or in the Or row are included in the result.

Now you will modify the query by entering alternate criteria in the Criteria and Or rows.

  1. Switch back to Design view.
  2. Remove the Is Not Null AND <>"" string from the Criteria row of the Phone field.
  3. In the Or row of the Phone field, type Is Not Null AND <>"".
  4. In the Or row of the City field, type london.
  5. Switch to Datasheet view to see the results. All records that include either an address, a phone number, or both, and where City is London, are displayed in the query results.

 Note   To specify more than two alternate criteria sets, use the rows below the Or row. Each row represents an independent set of criteria.

ShowReview the SQL statement with revised criteria

Click SQL View on the Access status bar, or right-click the query document tab and then click SQL View. Access displays the following SQL code:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City]
FROM Customers
WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"") AND ((Customer.[City])="london"))
 OR (((Customers.[Phone]) Is Not Null And (Customers.[Phone])<>"") AND ((Customers.[City])="london"));

You will note that two things have changed: the criterion that specifies that City is London now appears twice, and the criteria in the WHERE clause are now combined by using the OR operator.


To learn more about how to specify criteria for various types of fields, see Examples of query criteria.

Top of Page Top of Page

Add calculations to the query

A well designed database does not store simple calculated values in tables. For example, a table might store a person's date of birth but not their current age. If you know both today's date and the person's date of birth, you can always calculate their current age, so there is no need to store that in the table. Instead, you create a query that calculates and displays the pertinent value. The calculations are made every time you run the query, so if the underlying data changes, so do your calculated results.

In this exercise, you will modify the London Contacts query so that it displays each contact's date of birth and current age.

  1. Open the query in Design view.
  2. From the Customers table window, drag the BirthDate field to the first blank column in the design grid. You can also double-click the field name to automatically add it in the first blank column.
  3. In the next column, in the Field row, type the expression that will calculate the age for each record. Type Age: DateDiff ("yyyy", [BirthDate], Date()).

Age is the name you are using for the calculated field. If you do not supply a name, Access will use a generic name for the field, for example, EXPR1. The string following the colon (:) is the expression that supplies the values for each record. The DateDiff function calculates the difference between any two dates, and returns that difference in the specified format. The format, yyyy, returns the difference in years, and the [BirthDate] and Date() elements of the expression supply the two date values. Date is a function that returns the current date, and [BirthDate] refers to the BirthDate field in the underlying table.

 Note   The calculation used for Age in this example is an approximation that may be slightly inaccurate, depending on the current month.

Switch to Datasheet view. You see two additional fields, BirthDate and Age, in the result.

ShowReview the SQL statement with calculations

Click SQL View on the Access status bar, or right-click the query document tab and then click SQL View. Access displays the following SQL code:

SELECT Customers.[Contact], Customers.[Address], Customers.[Phone], Customers.[City], Customers.[BirthDate],
 DateDiff("yyyy",[BirthDate],Date()) AS Age
FROM Customers
WHERE (((Customers.[Address]) Is Not Null And (Customer.[Address])<>"") AND ((Customer.[City])="london"))
 OR (((Customers.[Phone]) Is Not Null And (Customers.[Phone])<>"") AND ((Customers.[City])="london"));

Note that Customers.[BirthDate], and the calculated field, Age, now appear in the SELECT clause. The AS keyword is used to designate the name of the calculated field.


For more information about how to write expressions that calculate values in a query, see the article Create an expression.

Top of Page Top of Page

Summarize query values

Summarizing the columns in a query is easier in Office Access 2007, compared to the same task in earlier versions of Access. You can add, count, or calculate other aggregate values, and display them in a special row (called the Total row) that appears below the asterisk (*) row in Datasheet view.

You can use a different aggregate function for each column. You can also choose not to summarize a column.

In this exercise, you will modify the London Contacts query so that it displays the Total row.

  1. Open the query in Datasheet view.
  2. On the Home tab, in the Records group, click Totals.
  1. Click the Total row in the Contact column.

In the drop-down list, you can choose between None and Count. Because the Contact column displays text values, other functions, such as Sum and Average, are not relevant, and are therefore not available.

  1. Select Count to count the number of contacts that are displayed in the result.

The number 5 is displayed in the Total row.

  1. In the Age field, select Average. Because the Age field evaluates to a number, it supports the Sum,Average, Count, Maximum, Minimum, Standard Deviation and Variance functions.

Access displays the average age in the Total row.

To clear the total for a column, click in the Total row under that column, then select None from the drop-down list. To hide the Total row, on the Home tab, in the Data Type & Formatting group, click Totals.

 Note   Using the Total row does not change the underlying SQL statement.

Top of Page Top of Page

 
 
Applies to:
Access 2007