| | Product Information Help and How-to Training Templates Support and Feedback Technical Resources Additional Resources | Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
About select and crosstab queries (MDB)
Select and crosstab queries are the two main ways you query a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) to retrieve just the data you want.  Select queries
What is a select query?
A select query is the most common type of query. You use it to:
 Bring together data from multiple tables and sort it in a particular order.
 Perform calculations on groups of records.
Creating a select query
You create a query with a wizard or from scratch in query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). In Design view, you specify the data you want to work with by adding the tables or queries that contain the data, and then by filling in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).
Field lists (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) show the fields in the tables or queries you add to your query.
A join line tells Microsoft Access how data in one table or query is related to data in another table or query.
 You add fields to the design grid by dragging them to the field lists.
 The fields, sort order, and criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) you add to the design grid determine what you will see in the query's results.
Crosstab queries
What is a crosstab query?
You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information— one down the left side of the datasheet and another across the top.
This select query only groups the totals vertically by employee and category. This results in more records, making comparisons between different employees' totals more difficult.
A crosstab query displays the same information, but groups it both horizontally and vertically so the datasheet is more compact and easier to analyze.
Creating a crosstab query
You create a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.) with a wizard or from scratch in query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). In the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), you specify which field's values will become column headings, which field's values will become row headings, and which field's values to sum, average, count, or otherwise calculate.
The settings in these rows determine how data is shown.
This setting displays the field's values as row headings.
This setting displays the field's values as column headings.
These settings display the total orders.
Let Microsoft Access create a select or crosstab query for you
Access can often create a query for you so you don't have to design one from scratch.
- To create a query to use as the basis of a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), try the form, report, or data access page wizards. They create the form, report or data access page, and if it's based on more than one table, they also create its underlying SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). If you want, you can save the SQL statement as a query.
- To easily create queries that you want to run independently or base multiple forms, reports, and data access pages on, try one of the query wizards. Query wizards do all the basic work for you after you provide answers to a series of questions. Even if you've created many queries, you may want to use a wizard to quickly design the query. Then you can switch to Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) to customize it.
- To create queries from filters (filter: A set of criteria applied to data in order to display a subset of the data or to sort the data. In Access, you can use filtering techniques such as Filter By Selection and Filter By Form to filter data.) you created using Filter By Form (Filter By Form: A technique for filtering data that uses a version of the current form or datasheet with empty fields in which you can type the values that you want the filtered records to contain.), Filter By Selection (Filter By Selection: A technique for filtering records in a form, datasheet, or data access page in which you retrieve only records that contain the selected value.), or Filter For Input (Filter For Input: A technique for filtering records that uses a value or expression that you enter to find only records that contain the value or satisfy the expression.), save the filter as a query.
If the wizard doesn't start This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.
For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later, at http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010489351033.
For more information about sandbox mode, see the topic About Microsoft Jet Expression Service sandbox mode.
If none of these methods satisfies your needs, you can create the query from scratch in query Design view.
|
United States
Email this link
Printer-Friendly Version
Share
Hide All
