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
 
- Windows 7 : Using System Protection (part 1) - Turning System Protection on or off
- Windows Server 2008 : Understanding Group Policy Settings (part 1) - Enabling Auditing Through Group Policy
- Feature Overview and Benefits of Microsoft Lync Server 2013 : Dial-In Conferencing
- Microsoft Lync Server 2013 : Director Overview (part 1) - Benefits of a Director - Internal Endpoint Sign-In Process
- Active Directory 2008 : Monitoring and Troubleshooting Active Directory Replication
- Windows Server 2012 : Preparing for deploying domain controllers (part 1) - AD DS deployment scenarios
- Windows 7 : Working with Files - Restore a Deleted File
- Windows Phone 8 : Groups (part 4) - Renaming a Group
- Windows 7 : Windows Media Player - Getting Music and Video on Your Computer
- Windows Phone 8 : Scheduled Notifications (part 1)
 
 
Top 10
 
- 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
- Securing an Exchange Server 2007 Environment : Protecting Against Spam (part 1) - Protecting Against Web Beaconing
- Securing an Exchange Server 2007 Environment : Securing Outlook 2007 (part 2) - Encrypting Communications Between Outlook and Exchange , Blocking Attachments
- Securing an Exchange Server 2007 Environment : Securing Outlook 2007 (part 1) - Outlook Anywhere
- Securing an Exchange Server 2007 Environment : Securing Your Windows Environment (part 3) - Keeping Up with Security Patches and Updates
- Securing an Exchange Server 2007 Environment : Securing Your Windows Environment (part 2) - Utilizing Security Templates
- Securing an Exchange Server 2007 Environment : Securing Your Windows Environment (part 1) - Windows Server 2003 Security Improvements , Windows Vista Security Improvements
- Securing an Exchange Server 2007 Environment : Client-Level Secured Messaging - Exchange Server 2007 Client-Level Security Enhancements