Access 2016 For Dummies
Book image
Explore Book Buy On Amazon

The basic query tool in Access 2016, created to make your life easier, is the Select query — so named because it selects matching records from your database and displays the results according to your instructions.

The best process for creating a Select query depends on the following:

  • If you’re new to writing queries, the Query Wizard is a fast, easy way to get started. It walks you through the process of selecting tables and fields for your query — and can even add some summary calculations (such as counting records) to your query.

  • If you’ve already written some queries and are comfortable with the Query Design window, you’ll probably want to bypass the Query Wizard and build your queries from scratch.

Solid relationships are the key to getting it all (from your tables)

In life, solid relationships make for a happier person; in Access, solid relationships make for a happier query experience.

To query your database effectively, you need to know the following about its table structure:

  • Which tables do you need to use?

  • How are the tables you need to use related to each other?

  • Which fields contain the data you want to know about?

  • Which fields do you need in the solution?

Access maintains relationships between the tables in your database. Usually you (or your Information Systems department) create these relationships when you first design the database. When you build the tables and organize them with special key fields, you actually prepare the tables to work with a query.

Key fields relate your Access tables to each other. Queries use key fields to match records in one table with their related records in another table. You can pull data for the item you seek from the various tables that hold this data in your database — provided they’re properly related before you launch the query.

If you don’t relate your tables via the Relationships window, you’ll have to do so for each multiple-table query you build in Access. As a general rule, put in the time to properly design and relate your tables. With proper table design and relationships, you’ll get the results you want in a shorter amount of time.

Running the Query Wizard

You can rely on the Query Wizard — and the Simple Query Wizard found within it — for a real dose of hands-free filtering. With the Simple Query Wizard, you enter table and field information. The wizard takes care of the behind-the-scenes work for you.

Access isn’t psychic (that’s scheduled for the next version); it needs some input from you!

To create a query with the Query Wizard’s Simple Query Wizard, follow these steps:

  1. On a piece of paper, lay out the data you’d like in your query results.

    A query returns a datasheet (column headings followed by rows of data), so make your layout in that format. All you really need are the column headings so you’ll know what data to pull from the database.

  2. Determine the table location of each piece of data (column heading) from your paper.

    Write down the table and field name that contain the data matching the column heading on the paper above the column heading.

  3. In the Database window, click the Create tab on the Ribbon and then click the Query Wizard button from the Queries section.

    The New Query Wizard dialog box appears, asking you what kind of Query Wizard you’d like to run. Choose Simple Query Wizard and click OK.

  4. Choose the first table you want to include in the query.

    The Simple Query Wizard starts and asks which table(s) you want to query.
    The Simple Query Wizard starts and asks which table(s) you want to query.

    You’ll use the Tables/Queries drop-down menu, which shows all the tables (and any existing queries) in your database. Here are the specifics:

    1. Click the down arrow next to the Tables/Queries drop-down menu.

      The Tables/Queries drop-down list.
      The Tables/Queries drop-down list.
    2. Click the name of the table or query to include in this query.

  5. Select the fields from that table or query for your query.

    Repeat these steps to select each field you want included in your query:

    1. Click the name of the table or query to include in this query.

      The Available Fields list changes and displays the fields available in the table.

    2. In the Available Fields list, double-click each field from this table or query that you want to include in the query you’re creating.

    If you add the wrong field, just double-click it in the Selected Fields list. It will go back home. If you just want to start all over, click the double-left chevron (that’s what you call the symbol that looks like a less-than sign) and all the selected fields go away.

  6. After you select all the fields, click Next.

    If the wizard can determine the relationships between the tables you selected, this window appears.

    The Query Wizard may give you the chance to summarize your data.
    The Query Wizard may give you the chance to summarize your data.

    If you don’t see the window, not to worry. Access just wants you to name the query instead. Skip to Step 8.

    If you include fields from two tables that aren’t related, a warning dialog box appears. The dialog box reminds you that all the selected tables must be related before you can run your query — and suggests that you correct the problem before continuing. In fact, it won’t let you go any further until you appease it in one of two ways:

    • Remove all the fields selected for your query from the unrelated tables.

    • Fix the relationships so that all tables you’ve selected in your query are related.

  7. If the wizard asks you to choose between a Detail and a Summary query, click the radio button next to your choice and then click Next.

    • Detail creates a datasheet that lists all records that match the query. As the name implies, you get all the details from those records.

    • Summary tells the wizard that you aren’t interested in seeing every single record; you want to see a summary of the information instead.

      A summary query can perform calculations (such as sums and averages) on numeric fields. If text fields are selected, Access can count the records or pull the first and last item from the set of fields alphabetically.

      If you want to make any special adjustments to the summary, click Summary Options to display the Summary Options dialog box. Select your summary options from the check boxes for the available functions — Sum, Avg, Min, and Max — and then click OK.

      Access offers different ways of summarizing the data.
      Access offers different ways of summarizing the data.
  8. In the wizard page that appears, select a radio button for what you want to do next:

    • If you want to make your query snazzy: Select the Modify the Query Design option.

      The wizard sends your newly created query to the salon for some sprucing up, such as the inclusion of sorting and totals.

    • If you want to skip the fancy stuff: Select the Open the Query to View Information option to see the Datasheet view.

    The wizard runs the query and presents the results in a typical Access datasheet.

  9. Type a title for your query in the text box and then click Finish.

    The wizard builds your query and saves it with the title you entered; then Access displays the results.

    Congratulations! You’ve given birth to a query.

    The results of a query built with the Query Wizard.
    The results of a query built with the Query Wizard.

When you finish the steps in this section, the Query Wizard saves your query automatically with the name you typed.

About This Article

This article is from the book:

About the book authors:

Laurie Ulrich Fuller is a professional technology author and trainer. She's created training materials that cover Microsoft Office and Adobe Creative Suite. Ken Cook is a professional database developer, instructor, and author. The two experts have teamed to write the previous three editions of Access For Dummies.

This article can be found in the category: