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
 
- Microsoft Exchange Server 2013 : Creating special-purpose mailboxes (part 8) - Creating arbitration mailboxes, Creating Discovery mailboxes
- Windows Server 2012 : A complete virtualization platform (part 7) - Enhanced quality of service
- Microsoft Visio 2010 : Cleaning Up Documents - Setting Document Properties,Removing Personal Information , Reducing File Size
- Microsoft Project 2010 : Setting Up Project for Your Use - Defining Environment Options (part 6)
- Microsoft PowerPoint 2010 : Preparing a Slide Show - Working with Fonts
- Microsoft Excel 2010 : Working with Other Microsoft Office Programs - Creating Hyperlinks
- Developing, Integrating, and Building Applications in Sharepoint 2013 (part 3) - User Interface Integration - Ribbon and Action Menus
- Microsoft Lync Server 2013 Edge Server : Edge Server Administration (part 3) - Managing XMPP Federation
- Windows Server 2012 : Understanding Internet Information Services 8 (part 1) - Improvements in IIS 8
- Migrating to Exchange 2013 : Legacy Exchange Migrations, Common Migration Problems
 
 
Top 10
 
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 3) - Controlling Output
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 2) - PowerShell Help , PowerShell Variables
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 1) - Listing the SharePoint Commands
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Commands
- Microsoft Sharepoint 2013 : Microsoft SharePoint 2013 Management Shell and Other Hosts
- Microsoft Word 2010 : Using Advanced Text Features - Addressing Envelopes
- Microsoft Word 2010 : Using WordArt (part 2) - Changing the Format of a WordArt Object
- Microsoft Word 2010 : Using WordArt (part 1) - Inserting WordArt Text
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 3) - Formatting Text in a Text Box, Linking Text Boxes
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 2) - Resizing a Text Box , Moving a Text Box