Access select query

Access select query DEFAULT

MS Access - Query Data

A query is a request for data results, and for action on data. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data.

  • As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table.

  • With a query you can apply a filter to the table's data, so that you only get the information that you want.

  • Queries that you use to retrieve data from a table or to make calculations are called select queries.

  • Queries that add, change, or delete data are called action queries.

  • You can also use a query to supply data for a form or report.

  • In a well-designed database, the data that you want to present by using a form or report is often located in several different tables.

  • The tricky part of queries is that you must understand how to construct one before you can actually use them.

Create Select Query

If you want to review data from only certain fields in a table, or review data from multiple tables simultaneously or maybe just see the databased on certain criteria, you can use the Select query. Let us now look into a simple example in which we will create a simple query which will retrieve information from tblEmployees table. Open the database and click on the Create tab.

Create Select

Click Query Design.

Query Design

In the Tables tab, on the Show Table dialog, double-click the tblEmployees table and then Close the dialog box.

Show Table

In the tblEmployees table, double-click all those fields which you want to see as result of the query. Add these fields to the query design grid as shown in the following screenshot.

Add Fields

Now click Run on the Design tab, then click Run.


The query runs, and displays only data in those field which is specified in the query.


Access 2016: Designing a Simple Query

Lesson 8: Designing a Simple Query



The real power of a relational database lies in its ability to quickly retrieve and analyze your data by running a query. Queries allow you to pull information from one or more tables based on a set of search conditions you define. In this lesson, you will learn how to create a simple one-table query.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.

Watch the video below to learn more about designing a simple query in Access.

What are queries?

Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want.

How are queries used?

Queries are far more powerful than the simple searches or filters you might use to find data within a table. This is because queries can draw their information from multiple tables. For example, while you could use a search in the customers table to find the name of one customer at your business or a filter on the orders table to view only orders placed within the past week, neither would let you view both customers and orders at once. However, you could easily run a query to find the name and phone number of every customer who's made a purchase within the past week. A well-designed query can give information you might not be able to find out just by examining the data in your tables.

When you run a query, the results are presented to you in a table, but when you design one you use a different view. This is called Query Design view, and it lets you see how your query is put together.

Click the buttons in the interactive below to learn how to navigate the Query Design view.

One-table queries

Let's familiarize ourselves with the query-building process by building the simplest query possible: a one-table query.

We will run a query on the Customers table of our bakery database. Let's say our bakery is having a special event, and we want to invite our customers who live nearby because they are the most likely to come. This means we need to see a list of all customers who live close by, and only those customers.

We want to find our customers who live in the city of Raleigh, so we'll search for "Raleigh" in the City field. Some customers who live in the suburbs live fairly close by, and we'd like to invite them as well. We'll add their zip code, 27513, as another criteria.

If you think this sounds a little like applying a filter, you're right. A one-table query is actually just an advanced filter applied to a table.

To create a simple one-table query:

  1. Select the Create tab on the Ribbon, and locate the Queries group.
  2. Click the Query Design command.
    Clicking the Query Design Command
  3. Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you want to run a query on. We are running a query on our customers, so we'll select the Customers table.
    Selecting a table to use in the query
  4. Click Add, then click Close.
  5. The selected table will appear as a small window in the Object Relationship pane. In the table window, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen. In our example, we want to mail invitations to customers who live in a certain area, so we'll include the FirstName, Last Name, Street Address, City, and Zip Code fields.
    Selecting fields to add to the query
  6. Set the search criteria by clicking the cell in the Criteria: row of each field you want to filter. Typing criteria into more than one field in the Criteria: row will set your query to include only results that meet all criteria. If you want to set multiple criteria but don't need the records shown in your results to meet all of them, type the first criteria in the Criteria: row and additional criteria in the or: row and the rows beneath it. Because we want to find customers who either live in Raleigh or in the 27513 zip code, we'll type "Raleigh" in the City field and "27513" into the or: row of the Zip Code field. The quotation marks will search these fields for an exact match.
    Setting the search criteria so that the query will find records with either "Raleigh" in the City field or "27513" in the Zip Code field.
  7. After you have set your criteria, run the query by clicking the Run command on the Design tab.
    Clicking Run
  8. The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type the desired name, then click OK.
    Naming the new query to save it

Now you know how to create the simplest type of query with only one table. In the next lesson, you'll learn how to create a query that uses multiple tables.


  1. Open our practice database.
  2. Create a new query.
  3. Select the Customers table to include in your query.
  4. Add the following fields from the Customers table to your query:
    First Name
    Last Name
    Zip Code
  5. Set the following criteria:
    In the City field, type "Durham" to return only records with Durham in the City field.
    In the Zip Code field, type "27514" in the or: row to return records that are either in Durham or zip code 27514.
  6. Run the query. If you entered the query correctly, your results will include customers who live in Durham OR in zip code 27514.
  7. Save the query with the name Customers who live in Durham.


  1. Astrology decans personality
  2. Reload microsoft office
  3. Walmart monterey jack cheese
  4. Corner stand walmart
  5. Unifi default ip

Creating Your First Select Query

MS-Access/ Getting Started

Quite often, when you are working with or analyzing data, it is preferable to work with smaller sections of the data at a time. The tables contain all the records pertaining to a particular entity, but perhaps for your purposes you need to examine a subset of that data. Typically, the subsets are defined by categories or criteria. The select query enables you to determine exactly which records will be returned to you.

If you thought that creating queries required learning a programming language or some other technological hurdle, you are mistaken. Although it is possible to create queries using the programming language of databases (SQL), Access provides a graphical interface that is easy to use and quite user-friendly. This graphical interface has been called the QBE (Query by Example) or QBD (Query by Design) in the past. Now Microsoft calls it the Query Design view. In the Query Design view, tables and columns are visually represented, making it easy to visualize the question you would like to ask of the data.

Go up to the application ribbon and select the Create tab. From there, select Query Design. The Show Table dialog box now opens on top of a blank Query Design interface, as shown in Figure below. The white grid area you see in the Query Design view is often called the query grid.

Show Table dialog box

When creating your question of the data, the first thing you must determine is from which tables you need to retrieve data. The Show Table dialog box enables the user to select one or more tables. As you can see in Figure above, there are also tabs for Queries and Both. One of the wonderful features of queries is that you are not limited to just querying directly from the table. You can create queries of other queries.

For this first query, select the CustomerMaster table, either by selecting the table in the list and clicking Add or by double-clicking on the table in the list. Now that you have selected the table from which you want to retrieve data, you can close the Show Table dialog box and select the fields of that table that you would like to retrieve.

The Query Design view is divided into two sections. The top half shows the tables or queries from which the query will retrieve data. The bottom half (often called the query grid) shows the fields from which the query will retrieve data. You will notice in Figure below that the CustomerMaster table shown at the top half of the Query Design view lists all the fields but has an asterisk at the top of the list. The asterisk is the traditional database symbol that means that all fields from that table will be in the output.

Query Design view

For this example, select the following three fields: Branch_Num, Customer_Name, and State. To select fields, you can either double-click the field or click it once and drag it down to the bottom half (the query grid). Each field that you add to the query grid will be included in the output of the query. Figure below shows you how your query should look after selecting the output fields.

Shows the Output Fields of the Select Query

At this point, you have all you need to run the query. To run the query, click the Run button located on the Design tab. The output from a query looks similar to a regular table after it is open.

NOTE: To return to the Query Design view, simply click View on the Home tab and then select Design View.

Sorting Query Results

Now examine how you can sort the results of this query. Just as you sorted in Excel, you are going to select a column and choose between an ascending sort and a descending sort. In the query grid, notice the Sort row of the grid. This is where you can select either one or multiple sort columns. If you select multiple sort columns, the query will sort the results in order of left to right.

Go to the State column and click your mouse on the Sort section. As shown in Figure below, a drop-down box appears, enabling you to select either Ascending or Descending for that particular column.

The sort order options for a column are provided by the Query Design view.

Select Ascending and rerun the query. When you ran the query before, the states were in no particular order. After setting the sort order of the State column to ascending, the query output simply looks better and more professionally formatted.

The results of the query are now sorted in ascending order by the State field.

Filtering Query Results

Next, you will examine how you can filter the query output so that you retrieve only the specific records to analyze. As in Excel, in Access this filter is also called Criteria. Note the Criteria row in the query grid. This is where you will enter the value or values for which you would like to query. When entering a value in the Criteria section, all records that match it are returned in the query output. When entering text, you must enclose the text string with quotation marks. You can either place them there yourself or type your text and click another part of the query grid. Access then automatically places quotation marks around your criteria if the field you are filtering is a text field.

In the example, your manager wants to see the list of customers from California. Since California is designated by the abbreviation CA in the table, that is exactly what you will enter in the Criteria row of the State column.

After you run the query, you will notice that fewer records are returned. This is obvious from looking at the Record Selector at the bottom of the query output window. Aquick scan of the results verifies that indeed only records with CA in the State column were returned.

You can sort and filter query results just as if they were a Table. Simply click the drop-down arrow next to each of the column headings to active the sorting and filtering context menu.

Querying Multiple Tables

How you can perform a query on multiple tables. Remember that you split your data into separtate tables. You used Relationships to define the logical relationships between the data. Now you will query from the tables based on the relationships that were established.

For example, say you want to see the customer transactions from California. A quick examination of the TransactionMaster reveals that there is no State field on which you can filter. However, you see that there is a CustomerNumber field. In your Access relationships, you defined a one-tomany relationship between the Customer_Number primary key in CustomerMaster and the Customer_Number foreign key in the TransactionMaster table. Another way to think of it is filtering the TransactionMaster indirectly by filtering a table that is related to it and using those results to determine which TransactionMaster records to return.

In the query that you already have opened, add the TransactionMaster table so you can include some fields from that table in your query output. Right-click the top half of the Query Design view and select Show Table. Double-click the TransactionMaster table to add it to the Query Design view. You will notice that the previously established relationship is automatically represented, as shown in Figure below. You can see the one-to-many relationship, indicating possible multiple records in TransactionMaster for each individual customer in the CustomerMaster table.

Relationship between two Tables

You must now select the fields from your newly added table, which you need to appear in the query output. Examine the individual invoices and invoice amounts that were issued to customers from California. Select the following three fields from the TransactionMaster table: Invoice_Number, Invoice_Date, and Sales_Amount. The field names from the two tables are brought together in the query grid.

Figure below show, you now have the invoice data matched with its appropriate customer data. Although there is repeating data, as with your flat-file examples, there is a significant difference. The repeating data is being read from a single source, the CustomerMaster table. If a value were to change in the CustomerMaster table, that changed value would be repeated in your query results. You have overcome potential update errors inherent with duplicate data.

Query Matched Data from two Separate Tables

Refining the Query

You can narrow your results down even further by filtering the query results according to a certain date. As you can see, there are several rows of criteria cells. These enable you to enter multiple criteria from which to filter. One thing to keep in mind is that each separate criteria row functions as its own separate set of criteria. Take a look at how this works.

Click the Criteria cell in the Invoice_Date column and type 4/20/2004. When you click outside that cell, you will notice that number signs (#) now surround the date. When running this query, only results matching the two criteria (State = 'CA' and Invoice_Date = 4/20/2004) are returned.

Now look at using multiple criteria for a single field. For example, say you want to bring in invoices for the data 11/19/2004 as well as 4/20/2004. You will want to add the new criteria line below the existing criteria. This will have the effect of testing the records for either one criteria or the other.

Since you want to limit your query to only results from California, you must retype "CA" on your new Criteria line. If you do not do that, the query will think that you want all invoices from California on 4/20/2004 or invoices from all states on 11/19/2004. The criteria lines will be evaluated individually. Add "CA" to the state column under the existing "CA".

After running the query, you can see your results have been refined even further. You have only those invoices from California that were issued on November 19, 2004 and April 20, 2004. To use multiple criteria in a query, you are not limited to using the separate criteria lines. By using operators, you can place your multiple criteria on the same line.

Using Operators in Queries

You can filter for multiple criteria on any given field by using operators. The following operators enable you to combine multiple values in different logical contexts so you can create complex queries:

  • Or: Either condition can be true. Multiple criteria values for one field can either be separated on different criteria lines or combined in one cell with the use of the Or operator. For example, using your query you can filter for both California and Colorado by typing "CA" or "CO" in the Criteria field.
  • Between: Tests for a range of values. For example, using your query you can filter for all invoices between 4/20/2004 and 11/19/2004 instead of testing just for those particular dates by typing Between #4/20/2004# and #11/19/2004# in the Criteria field.
  • Like: Tests for string expressions matching a pattern. For example, you can filter for all records with a customer number that begins with the number 147 by typing Like "147*" in the Criteria field. The asterisk is the wild card character, which can signify any character or combination of characters.
  • In: Similar to Or. Tests for all records that have values contained in parentheses. For example, you can filter for both California and Colorado by typing In ("CA", "CO") in the Criteria field.
  • Not: Opposite of writing a value in Criteria. All records not matching that value will be returned. For example, you can filter for all states except California by typing Not "CA" in the Criteria field.
  • Is Null: Filters all records that have the database value Null in that field.
  • =, <, >, <=, >=, and <>: The traditional mathematical operators allow you to construct complex criteria for fields that are used in calculations.

For example, suppose you want to further refine your query so that only invoice amounts over $200 will be returned in the results. use the greater-than operator to filter the Sales_Amount.

After running the query, you can see that you narrowed your results down to just six records. These are the only records that match the multiple criteria that were designated in the query grid.

Exporting Query Results

Now that you have covered the basics of creating queries, you need to be able to export these results to Excel or another format. The simplest way to do this in Access is to right-click the query after it has been saved. Select Export and choose the appropriate file type. The query will take a snapshot of the data and save the results in the requested format.


Access: Designing a Simple Query

SELECT statement (Microsoft Access SQL)

  • 5 minutes to read

Applies to: Access 2013 | Office 2013

Instructs the Microsoft Access database engine to return information from the database as a set of records.


SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, …]]} FROM tableexpression [, …] [IN externaldatabase] [WHERE… ] [GROUP BY… ] [HAVING… ] [ORDER BY… ] [WITH OWNERACCESS OPTION]

The SELECT statement has these parts:


One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.


Specifies that all fields from the specified table or tables are selected.


The name of the table containing the fields from which records are selected.

field1, field2

The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.

alias1, alias2

The names to use as column headers instead of the original column names in table.


The name of the table or tables containing the data you want to retrieve.


The name of the database containing the tables in tableexpression if they are not in the current database.

To perform this operation, the Microsoft Jet database engine searches the specified table or tables, extracts the chosen columns, selects rows that meet the criterion, and sorts or groups the resulting rows into the order specified.

SELECT statements do not change data in the database.

SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT…INTO statements.

The minimum syntax for a SELECT statement is:

SELECT fields FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the Employees table:

If a field name is included in more than one table in the FROM clause, precede it with the table name and the . (dot) operator. In the following example, the Department field is in both the Employees table and the Supervisors table. The SQL statement selects departments from the Employees table and supervisor names from the Supervisors table:

When a Recordset object is created, the Microsoft Jet database engine uses the table's field name as the Field object name in the Recordset object. If you want a different field name or a name is not implied by the expression used to generate the field, use the AS reserved word. The following example uses the title Birth to name the returned Field object in the resulting Recordset object:

Whenever you use aggregate functions or queries that return ambiguous or duplicate Field object names, you must use the AS clause to provide an alternate name for the Field object. The following example uses the title HeadCount to name the returned Field object in the resulting Recordset object:

You can use the other clauses in a SELECT statement to further restrict and organize your returned data. For more information, see the Help topic for the clause you are using.

Links provided by the UtterAccess community. UtterAccess is the premier Microsoft Access wiki and help forum.


Some of the following examples assume the existence of a hypothetical Salary field in an Employees table. Note that this field does not actually exist in the Northwind database Employees table.

This example creates a dynaset-type Recordset based on an SQL statement that selects the LastName and FirstName fields of all records in the Employees table. It calls the EnumFields procedure, which prints the contents of a Recordset object to the Debug window.

This example counts the number of records that have an entry in the PostalCode field and names the returned field Tally.

This example shows the number of employees and the average and maximum salaries.

The Sub procedure EnumFields is passed a Recordset object from the calling procedure. The procedure then formats and prints the fields of the Recordset to the Debug window. The variable is the desired printed field width. Some fields may be truncated.


Select query access

How to Create a Simple Select Query in Design View in Access 2016

The Query Design option is basically the "Design View" for queries. It offers more options for creating queries than the Query Wizard.

When creating a query, you can either click the Query Wizard button or Query Design button.

The Query Wizard is good for beginners, who are just trying to work out how to build a basic query.

However, once you've used the Query Wizard a few times, you'll probably understand enough about creating queries that you can jump over to Design View.

Clicking the button takes you to Design View. Design View enables you to quickly build a query, test it, modify it, test it again, and so on, as much as you need to.

Once you understand the basic building blocks of Design View, you'll probably never want to use the Query Wizard again for building simple queries.

Here's how to create a simple select query using Query Design.

  1. Screenshot of the Query Design button highlighted.
  2. Screenshot of selecting the tables.
  3. Screenshot of the query in Design View.
  4. Screenshot of the query in Design View with the query criteria.Close up of the query criteria.
  5. Screenshot of the result.
  6. Screenshot of the result.

Save the Query

If you plan to run the query again in the future, you should save it.

Saving a query is just like saving any other database object.

  1. Screenshot of saving the query.
  2. Screenshot of saving the query.
  3. Screenshot of saving the query.

Convert it to a Parameter Query

Turn the above query into a parameter query by asking the user to enter a population number to use.

Screenshot of a parameter query.
Access making a SQL Select Query

When you want to select specific data from one or more sources, you can use a select query. A select query helps you retrieve only the data that you want, and also helps you combine data from several data sources. You can use tables and other select queries as data sources for a select query. This topic provides an overview of select queries, and gives steps for creating a select query, by using the Query Wizard or in Design view.

If you want to use the Northwind sample database to learn more about how queries work, see the article Introduction to queries.

In this article


When you want to use data, you rarely want to use all of the data from one table. For example, when you want to use data from a Contacts table, you usually want to look at one specific record, or maybe just the telephone number. Sometimes you want to combine data from more than one table, such as combining Customer information with Order information. To select the data that you want to use, you use a select query.

A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two.

Benefits of using a query

A query lets you:

  • View data only from the fields you are interested in viewing. When you open a table, you see all the fields. A query is a handy way to save a selection of fields.

    Note: A query only points to data, it does not store data. When you save a query, you are not saving a copy of the data.

  • Combine data from several data sources. A table usually only displays data that it stores. A query lets you pick and choose fields from various sources, and specify how the information should be combined.

  • Use expressions as fields. For example, you could use the Date function as a field, or you could use the Format function with a field to control the way the data from the field is formatted in the query results.

  • View records that meet criteria that you specify. When you open a table, you see all the records. A query is a handy way to save a selection of records.

Basic steps to create a select query

You can create a select query by using the Query Wizard or by working in Design view. Some design elements are not available when you use the wizard, but you can add these elements later by using Design view. Although the two methods are somewhat different from each other, the basic steps are essentially the same:

  1. Choose the tables or queries that you want to use as sources of data.

  2. Specify the fields that you want to include from the data sources.

  3. Optionally, specify criteria to limit the records that the query returns.

After you have created a select query, you run it to see the results. To run a select query, you open it in Datasheet view. If you save the query, you can reuse it whenever you need, for example, as a data source for a form, report, or another query.

Use the Query Wizard to create a select query

You can use the Query Wizard to automatically create a select query. When you use the wizard, you have less control over the details of the query design, but the query is usually created faster than if you did not use the wizard. Moreover, the wizard can catch some simple design mistakes and prompt you to perform a different action.

Before you begin

If you use fields from data sources that are not related to each other, the Query Wizard asks you if you want to create relationships. The wizard opens the Relationships window for you, but you must restart the wizard if you edit any relationships. Therefore, before you run the wizard, consider creating any relationships that your query needs.

For more information about creating table relationships, see the article Guide to table relationships.

Use the Query Wizard

  1. On the Create tab, in the Queries group, click Query Wizard

    The Queries group in the Access ribbon displays two options: Query Wizard and Query Design
  2. In the New Query dialog box, click Simple Query Wizard, and then click OK.

  3. Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries.

    For each field, perform these two steps:

    1. Under Tables/Queries, click the table or query that contains the field.

    2. Under Available Fields, double-click the field to add it to the Selected Fields list. If you want to add all fields to your query, click the button with the double right arrows (>>).

    3. When you have added all the fields that you want, click Next.

      In the Simple Query Wizard dialog box, select the fields you want to use.
  4. If you did not add any number fields (fields that contain numeric data), skip ahead to step 9. If you added any number fields, the wizard asks whether you want the query to return details or summary data.

    Do one of the following:

    1. If you want to see individual records, click Detail, and then click Next. Skip ahead to step 9.

    2. If you want to see summarized numeric data, such as averages, click Summary, and then click Summary Options.

      Select Detail or Summary on the Simple Query Wizard dialog
  5. In the Summary Options dialog box, specify which fields you want to summarize, and how you want to summarize the data. Only number fields are listed.

    For each number field, choose one of the following functions:

    1. Sum    The query returns the sum of all the values of the field.

    2. Avg    The query returns the average of the values of the field.

    3. Min    The query returns the smallest value of the field.

    4. Max    The query returns the largest value of the field.

    Select how you want the summary values calculated on the Summary Options dialog
  6. If you want the query results to include a count of the records in a data source, select the appropriate Count records in data source name check box.

  7. Click OK to close the Summary Options dialog box.

  8. If you did not add a date/time field to the query, skip ahead to step 9. If you added a date-time field to the query, the Query Wizard asks you how you would like to group the date values. For example, suppose you added a number field ("Price") and a date/time field ("Transaction_Time") to your query, and then specified in the Summary Options dialog box that you want to see the average value of the number field "Price". Because you included a date/time field, you could calculate summary values for each unique date/time value, for each day, for each month, for each quarter, or for each year.

    Select how you want to group dates in your query on the Simple Query Wizard dialog

    Select the time period that you want to use to group the date/time values, and then click Next.

    Note: In Design view, you can use an expression to group by any time period you want, but the wizard only offers these choices.

  9. On the last page of the wizard, give the query a title, specify whether you want to open or modify the query, and then click Finish.

    If you choose to open the query, the query displays the selected data in Datasheet view. If you choose to modify the query, the query opens in Design view.

Top of Page

Create a query by working in Design view

You can use Design view to manually create a select query. When you use Design view, you have more control over the details of the query design, but it is easier to make design mistakes, and it can take longer than using the wizard.

Create a query

Step 1: Add data sources

When you use Design view, to add data sources, you add the data sources and fields in separate steps. However, you can always add more data sources later if you want.

  1. On the Create tab, in the Other group, click Query Design

    The Queries group in the Access ribbon displays two options: Query Wizard and Query Design
  2. Double-click each data source that you want to use or select each data source and then click Add.

    Show Table dialog in Access displaying table names

Automatic joins

When you add the data sources, if the sources already have relationships defined between them, those relationships are automatically added to the query as joins. Joins specify how data from related sources should be combined. Access also automatically creates a join between two tables if they have fields have compatible data types and one field is a primary key.

You might want to adjust the joins that Access creates. Access determines what type of join to create based on the relationship the join represents. If Access creates a join but there is no defined relationship, Access creates an inner join.

If Access automatically creates the correct joins when you add the data sources, you can skip ahead to Step 3: Add output fields.

Use the same data source several times

In some cases, you want to join two copies of the same table or query, called a self-join, that combines records from the same table when there are matching values in the joined fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead.

When you add a data source a second time, Access appends _1 to the name of the second instance. For example, if you added the Employees table twice, the second instance would be named Employees_1.

Step 2: Join related data sources

If the data sources that you add to a query already have relationships, Access automatically creates an inner join for each relationship. If referential integrity is enforced, Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol () to show which table is on the "many" side.

If you add queries to your query, and have not created relationships between those queries, Access does not automatically create joins between those queries, or between queries and tables that are not related. If Access does not create joins when you add data sources, you should usually add them yourself. Data sources that are not joined to any other data source can cause problems with the query results.

You might also want to change the type of a join from an inner join to an outer join, so that your query includes more records.

Add a join

  • To add a join, drag a field from one data source to a corresponding field on another data source.

    Access displays a line between the two fields to show that a join has been created.

    A join line in query Design view

Change a join

  1. Double-click the join you want to change.

    The Join Properties dialog box appears.

    The Join Properties dialog box
  2. In the Join Properties dialog box, review the three options.

  3. Click the option that you want to use, and then click OK.

After the joins are ready, you add output fields — fields that have data that you want in the query results.

Step 3: Add output fields

You can easily add a field from any of the data sources that you added in step 1.

  • To add a field, drag the field from a data source in the upper pane of the query design window down to the Field row of the design grid, in the bottom pane of the query design window.

    When you add a field this way, Access automatically fills in the Table row of the design grid to reflect the data source of the field.

    Tip: If you want to quickly add all fields down to the Field row of the query design grid, double-click the table or query name from the upper pane to highlight all the fields in that source and then drag them all down to the design grid at the same time.

Use an expression as an output field

If you want to perform calculations or use a function to produce query output, you can use an expression as an output field. An expression can use data from any of the query data sources, as well as functions, such as Format or InStr, and can also contains constants and arithmetic operators.

  1. In an empty column of the query design grid, right-click the Field row, and then click Zoom on the shortcut menu.

  2. In the Zoom box, type or paste your expression. Preface your expression with the name you would like to use for the expression output, followed by a colon. For example, if you wanted the expression to be labeled "Last updated", you would start your expression with Last updated:.

    Note: You can do a wide variety of things by using expressions. A thorough explanation of expressions is beyond the scope of this article. For more information about creating an expression, see the article Build an expression.

Step 4: Specify criteria

This step is optional.

You use criteria to limit the records that your query returns, on the basis of whether field values meet the criteria that you specify.

Specify criteria for an output field

  1. In the query design grid, in the Criteria row of the field that has values that you want to limit, type an expression that field values must satisfy to be included in your results. For example, if you wanted to limit a query so that only records where the value of the field City is Las Vegas, type Las Vegas in the Criteria row under that field.

    For many examples of query criteria for various data types, see the article Examples of query criteria.

  2. Specify any alternate criteria in the Or row, below the Criteria row.

    If you specify alternate criteria, a field value can meet any of the listed criteria and be included in the query result.

Multiple field criteria

You can use criteria with multiple fields. When you do, all the criteria in a given Criteria or Or row must be true for the record to be included.

Specify criteria by using a field that you don't want to output

You can add a field to your query design and not include the field's data in the query output. You do this if you want to use the field's values to limit the query results, but don't want to see the field values.

  1. Add the field to the design grid.

  2. Clear the check box in the Show row for the field.

  3. Specify criteria as you would for an output field.

Step 5: Summarize data

This step is optional.

You might want to summarize data, especially if your data is numeric. For example, you might want to see the average price, or total sales.

To summarize data in a query, you use the Total row. By default, the Total row is not displayed in Design view.

  1. With the query open in Design view, on the Design tab, in the Show/Hide group, click Totals.

    Access displays the Total row in the query design grid.

  2. For each field that you want to summarize, choose the function to use from the list in the Total row. The functions that are available depend on the data type of the field.

    To learn more about the Total row functionality in queries, see the article Sum or count values on a datasheet with a Total row.

Step 6: View the results

To see the query results, on the Design tab, click Run. Access displays the results of your query in Datasheet view.

To make further changes to the query, click Home > View > Design View to switch back to Design view.

Change your fields, expressions, or criteria and rerun the query until it returns the data that you want.

Top of Page


You will also like:

SELECT Query Syntax

SELECT Query Syntax

Hopefully, you've found the MS-Access query builder to be helpful in learning the basics of retrieving data from a database. As you move forward, it will become important for you to learn some of the syntax details (i.e., how to write the statements you saw in SQL View without the aid of a graphical query builder). That will be the focus of this part of the lesson.

A. The SELECT clause

All SELECT queries begin with a SELECT clause whose purpose is to specify which columns should be retrieved. The desired columns are separated by commas. Our first query in this lesson had a SELECT clause that looked like the following:


Note that each column name is preceded by the name of its parent table followed by a dot. This is critical when building a query involving joins and one of the desired columns is found in multiple tables. Including the parent table eliminates any confusion the SQL interpreter would have in deciding which column should be retrieved. However, you should keep in mind that the table name can be omitted when the desired column is unambiguous. For example, because our simple query above is only retrieving data from one table, the SELECT clause could be reduced to:


Selecting all columns

The easiest way to retrieve data from a table is to substitute the asterisk character (*) for the list of columns:


This will retrieve data from all of the columns. While it's tempting to use this syntax because it's so much easier to type, you should be careful to do so only when you truly want all of the data held in the table or when the table is rather small. Retrieving all the data from a table can cause significant degradation in the performance of an SQL-based application, particularly when the data are being transmitted over the Internet. Grab only what you need!

Case sensitivity in SQL

Generally speaking, SQL is a case-insensitive language. You'll find that the following SELECT clause will retrieve the same data as the earlier ones:

SELECT player_id, hr, rbi

Why did I say "generally speaking?" There are some RDBMS that are case sensitive depending on the operating system they are installed on. Also, some RDBMS have administrative settings that make it possible to turn case sensitivity on. For these reasons, I suggest treating table and column names as if they are case sensitive.

On the subject of case, one of the conventions followed by many SQL developers is to capitalize all of the reserved words (i.e., the words that have special meaning in SQL) in their queries. Thus, you'll often see the words SELECT, FROM, WHERE, etc., capitalized. You're certainly not obligated to follow this convention, but doing so can make your queries more readable, particularly when table and column names are in lower or mixed case.

Finally, recall that we renamed our output columns in some of the earlier query builder exercises by putting the desired column name and a colon in front of the input field name or expression. This sort of action in the query builder translates to an AS subclause in SQL, such as the following:


B. The FROM clause

The other required clause in a SELECT statement is the FROM clause. This specifies the table(s) containing the columns specified in the SELECT clause. In the first queries we wrote, we pulled data from a single table, like so:


Or, omitting the parent table from the column specification:


The FROM clause becomes a bit more complicated when combining data from multiple tables. The Access query builder creates an explicit inner join by default. Here is the code for one of our earlier queries:


An inner join like this one causes a row-by-row comparison to be conducted between the two tables looking for rows that meet the criterion laid out in the ON predicate (in this case, that the PLAYER_ID value in the PLAYERS table is the same as the PLAYER_ID value in the STATS table). When a match is found, a new result set is produced containing all of the columns listed in the SELECT clause. If one of the tables has a row with a join field value that is not found in the other table, that row would not be included in the result.

We also created a couple of outer joins to display data from records that would otherwise not appear in an inner join. In the first example, we chose to include all data from the left table and only the matching records from the right table. That yielded the following code:


Note that the only difference between this FROM clause and the last is that the INNER keyword was changed to the word LEFT. As you might be able to guess, our second outer join query created code that looks like this:


Here the word LEFT is replaced with the word RIGHT. In practice, RIGHT JOINs are more rare than LEFT JOINs since it's possible to produce the same results by swapping the positions of the two tables and using a LEFT JOIN. For example:


Recall that the cross join query we created was characterized by the fact that it had no line connecting key fields in the two tables. As you might guess, this translates to a FROM clause that has no ON predicate:


In addition to lacking an ON predicate, the clause also has no form of the word JOIN. The two tables are simply separated by commas.

Finally, we created a query involving two inner joins. Here is how that query was translated to SQL:


The first join is enclosed in parentheses to control the order in which the joins are carried out. Like in algebra, operations in parentheses are carried out first. The result of the first join then participates in the second join.

C. The WHERE clause

As we saw earlier, it is possible to limit a query's result to records meeting certain criteria. These criteria are spelled out in the query's WHERE clause. This clause includes an expression that evaluates to either True or False for each row. The "True" rows are included in the output; the "False" rows are not. Returning to our earlier examples, we used a WHERE clause to identify seasons of 100+ RBIs:


As with the specification of columns in the SELECT clause, columns in the WHERE clause need not be prefaced by their parent table if there is no confusion as to where the columns are coming from.

This statement exemplifies the basic column-operator-value pattern found in most WHERE clauses. The most commonly used operators are:

<>not equals
>greater than
>=greater than or equal to
<less than
<=less than or equal to
BETWEENwithin a value range
LIKEmatching a search pattern
INmatching an item in a list

The usage of most of these operators is straightforward. But let's talk for a moment about the LIKE and IN operators. LIKE is used in combination with the wildcard character (the * character in Access and most other RDBMS, but the % character in some others) to find rows that contain a text string pattern. For example, the clause WHERE FIRST_NAME LIKE 'B*' would return Babe Ruth and Barry Bonds. WHERE FIRST_NAME LIKE '*ar*' would return Barry Bonds and Harmon Killebrew. WHERE FIRST_NAME LIKE '*ank' would return Hank Aaron and Frank Robinson.


Text strings like those in the WHERE clauses above must be enclosed in quotes. Single quotes are recommended, though double quotes will also work in most databases. This is in contrast to numeric values, which should not be quoted. The key point to consider is the data type of the column in question. For example, zip code values may look numeric, but if they are stored in a text field (as they should be because some begin with zeroes), they must be enclosed in quotes.

The IN operator is used to identify values that match an item in a list. For example, you might find seasons compiled by members of the Giants franchise (which started in New York and moved to San Francisco) using a WHERE clause like this:


Finally, remember that WHERE clauses can be compound. That is, you can evaluate multiple criteria using the AND and OR operators. The AND operator requires that both the expression on the left and the expression on the right evaluate to True, whereas the OR operator requires that at least one of the expressions evaluates to True. Here are a couple of simple illustrations:


Sometimes WHERE clauses require parentheses to ensure that the filtering logic is carried out properly. One of the queries you were asked to write earlier sought to identify 100+ RBI seasons compiled before 1960 or after 1989. The SQL generated by the query builder looks like this:


It turns out that this statement is more complicated than necessary for a couple of reasons. The first has to do with the way I instructed you to build the query:

Screen capture of the result of RBI>99 criterion inefficiently being added to the query twice.

This resulted in the RBI>99 criterion inefficiently being added to the query twice. A more efficient approach would be to merge the two year criteria into a single row in the design grid as follows:

Screen capture of a more efficient version of the statement which puts a set of parentheses around the two year criteriaof the RBI condition, it produces a more readable query.

This yields a more efficient version of the statement:


It is difficult to see, but this version puts a set of parentheses around the two year criteria so that the condition of being before 1960 or after 1989 is evaluated separately from the RBI condition.

One reason the logic is difficult to follow is because the query builder adds parentheses around each column specification. Eliminating those parentheses and the table prefixes produces a more readable query:


D. The GROUP BY clause

The GROUP BY clause was generated by the query builder when we calculated each player's career RBI total:


The idea behind this type of query is that you want the RDBMS to find all of the unique values (or value combinations) in the field (or fields) listed in the GROUP BY clause and include each in the result set. When creating this type of query each field in the GROUP BY clause must also be listed in the SELECT clause. In addition to the GROUP BY fields, it is also common to use one of the SQL aggregation functions in the SELECT clause to produce an output column. The function we used to calculate the career RBI total was Sum(). Other useful aggregation functions include Max(), Min(), Avg(), Last(), First() and Count(). Because the Count() function is only concerned with counting the number of rows associated with each grouping, it doesn't really matter which field you plug into the parentheses. Frequently, SQL developers will use the asterisk rather than some arbitrary field. This modification to the query will return the number of years in each player's career, in addition to the RBI total:


Note: In actuality, this query would overcount the seasons played for players who switched teams mid-season (e.g., Mark McGwire was traded in the middle of 1997 and has a separate record in the STATS table for each team). We'll account for this problem using a sub-query later in the lesson.

E. The ORDER BY clause

The purpose of the ORDER BY clause is to specify how the output of the query should be sorted. Any number of fields can be included in this clause, so long as they are separated by commas. Each field can be followed by the keywords ASC or DESC to indicate ascending or descending order. If this keyword is omitted, the sorting is done in an ascending manner. The query below sorts the seasons in the STATS table from the best RBI total to the worst:


That concludes our review of the various clauses found in SELECT queries. You're likely to memorize much of this syntax if you're called upon to write much SQL from scratch. However, don't be afraid to use the Access query builder or other GUIs as an aid in producing SQL statements. I frequently take that approach when writing database-related PHP scripts. Even though the data are actually stored in a MySQL database, I make links to the MySQL tables in Access and use the query builder to develop the queries I need. That workflow (which we'll explore in more depth later in the course) is slightly complicated by the fact that the flavor of SQL produced by Access differs slightly from industry standards. We'll discuss a couple of the important differences in the next section.


1763 1764 1765 1766 1767