Office
 

Microsoft Access 2010 : Introducing Query Types & Creating a Query Using the Query Wizard

12/29/2011 9:22:44 AM

1. Introducing Query Types

When you want to retrieve table records that meet particular criteria, you create a query. The type of query you create, however, depends on the records you want to return and what, if anything, you want Access to do with the results.

The most basic query type is the select query, which reaches into one or more database tables and locates records. While you can have Access return every field in a record, you can also choose which fields are displayed in the results. For example, you could get information about customers that placed an order in a given month and, instead of displaying every field relating to the company, display just the company’s name. You can also limit the records returned by the query by specifying one or more criteria or rules the query uses when deciding which table rows to return. If your table contains data that relates to two different values, such as a company name and sales representatives, you can create a crosstab query to display the quantity of items sold by each employee to each company (as shown in the following figure).

A version of the select query is the parameter query. Like a select query, the parameter query uses one or more criteria to limit the records returned by the query. The difference, however, is that a parameter query lets the person running the query specify the criteria Access uses to decide whether or not to return a specific record. You can add a message to the criteria entry dialog box that lets the searcher know what kind of value to enter.

A separate type of query is the action query, which makes changes to the physical makeup of your database. You see two types of action queries in this article: the update query, which lets you change values in a table; and the make-table query, which writes query results to a new table in the current database (or another database entirely).

The final query type discussed in this section is the crosstab query. Unlike a select query, which presents its results in a worksheet, a crosstab query presents its results in a layout like that of a spreadsheet. Every value in the body of the query’s results is related to two other values. In this case, those values are your suppliers and your product categories.

As in a spreadsheet, you can choose the mathematical operation Access uses to summarize the data in the body of the crosstab query’s results. Available operations include finding a sum, average, the number of occurrences (as in the crosstab query results shown previously), or even the minimum or maximum value.

After you create a query (as shown following), you can display its results by double-clicking the query in the Navigation Pane. If the query is open in Design view, you can run it by clicking the Run button on the Design tab.


2. Creating a Query Using the Query Wizard

When you create a basic select query, you identify the table (or tables) with the data you want to find, name the fields to appear in the query results, and then save the query. The Query Wizard walks you through the process, making it easy to identify the tables and fields to appear in your query. What’s more, you can choose whether to have Access display detailed results (that is, the individual query rows) or summarize the query’s contents.

2.1. Create a Detail Query

  1. Click the Create tab.

  2. Click Query Wizard.

  3. Click Simple Query Wizard.

  4. Click OK.

  5. Click the Tables/Queries down arrow, and then click the table or query with the fields you want to use in your query.

  6. Click the first field to include in the query’s results.

  7. Click Add.

  8. Repeat steps 6 and 7 to add more fields (and step 5 to change the table or query from which you draw fields).

  9. Click Next.

  10. Click the Detail option button.

  11. Click Next.

  12. Type a name for your query.

  13. Click Finish.


Note:

To add all of a table’s fields to your query, click the Add All (>>) button. You can also remove a field by clicking the Remove (<) button or remove all fields by clicking the Remove All (<<) button.



Note:

The step of the wizard that asks whether you want to create a detail or summary query appears only for some types of queries; don’t panic if you don’t see it.


2.2. Create a Summary Query

  1. Click the Create tab.

  2. Click Query Wizard.

  3. Click Simple Query Wizard.

  4. Click OK.

  5. Click the Tables/Queries down arrow, and then click the table or query with the fields you want to use in your query.

  6. Click the first field to include in the query’s results.

  7. Click Add.

  8. Repeat steps 6 and 7 to add more fields (and step 5 to change the table or query from which you draw fields).

  9. Click Next.

  10. Click the Summary option button.

  11. Click Summary Options.

  12. Select the check boxes representing the summary values you want calculated.

  13. Click OK.

  14. Click Next.

  15. Select the option button representing how you want the query to group rows in the query’s source table.

  16. Click Next.

  17. Type a name for your query.

  18. Click Finish.

 
Others
 
- Microsoft Word 2010 : Creating Different Headers or Footers for Odd and Even Pages
- Microsoft Word 2010 : Editing a Header or Footer
- Microsoft PowerPoint 2010 : Working with Placeholders
- Microsoft PowerPoint 2010 : Changing the Background
- Microsoft Excel 2010 : Using Print Preview
- Microsoft Excel 2010 : Using Excel with the Normal Distribution - The Central Limit Theorem
- Microsoft Outlook 2010 : Configuring Mobile Alert Settings for Exchange Server Accounts
- Microsoft Outlook 2010 : Configuring the Exchange Server Client - Configuring Security Properties & Configuring Connection Properties
- Using OneNote Web App : Finding Out Who Wrote Notes & Using OneNote Web App in Office 365
- Microsoft PowerPoint 2010 : Creating and Managing Custom Color and Font Themes
- Microsoft PowerPoint 2010 : Changing Colors, Fonts, and Effects
- Microsoft Access 2010 : Preventing Database Problems
- Microsoft Access 2010 : Securing Databases for Distribution
- Microsoft Word 2010 : Inserting a Preformatted Header or Footer
- Microsoft Word 2010 : Inserting Preformatted Page Numbers
- Microsoft PowerPoint 2010 : Applying a Theme
- Microsoft PowerPoint 2010 : Understanding Layouts and Themes
- Microsoft Excel 2010 : Confidence Intervals and the Normal Distribution (part 3)
- Microsoft Excel 2010 : Confidence Intervals and the Normal Distribution (part 2)
- Microsoft Excel 2010 : Confidence Intervals and the Normal Distribution (part 1) - Constructing a Confidence Interval
 
 
Most View
 
- SQL Server 2012 : Configuration Options (part 1) - Displaying the Advanced Options
- Windows Phone 8 : Exploring the Execution Model (part 1) - Application State
- Microsoft Visio 2010 : Adding Structure to Your Diagrams - Finding Containers and Lists in Visio (part 1) - Swimlanes
- Windows 8 : Using the Control Panel Items (part 16) - Windows Update
- Windows 8 : Disks and Storage Devices - Drive Properties and Tools
- Microsoft SQL Server 2012 : Recovery Operations (part 2) - Performing the Restore with Management Studio
- Windows Phone 8 : Working with People - The People Hub (part 8) - Configuring Social Network Settings
- Creating Extended Events Sessions in SQL Server 2012 (part 4) - Counting the Number of Locks Acquired per Object , Creating Sessions Using T-SQL
- Microsoft Project 2010 : Strategic Importance of Project 2010
- Windows Server 2008 R2 Remote Desktop Services : Installing and Configuring Remote Desktop Services (part 7)
 
 
Top 10
 
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 3)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 2)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 1)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Divs and AP Elements 101
- Microsoft Project 2010 : Setting Up Project for Your Use - Defining Project Information (part 2) - Defining Project Properties
- Microsoft Project 2010 : Setting Up Project for Your Use - Defining Project Information (part 1) - Understanding the Project Information Dialog Box
- Microsoft Project 2010 : Setting Up Project for Your Use - Setting the Task Mode
- Securing an Exchange Server 2007 Environment : Securing Outlook Web Access
- Securing an Exchange Server 2007 Environment : Protecting Against Spam (part 2) - Filtering Junk Mail
- Securing an Exchange Server 2007 Environment : Protecting Against Spam (part 2) - Filtering Junk Mail