About analyzing data
Microsoft Access provides several techniques to help you analyze data.

Create a crosstab query

You can use a crosstab query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) to calculate and restructure data for easier analysis. 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 (datasheet: Data from a table, form, query, view, or stored procedure displayed in a row-and-column format.) and another across the top.



A select query

A crosstab query based on the select query

Add a PivotTable list to a data access page

A PivotTable list is an interactive table that you can use to analyze data dynamically from within a Web browser. At run time, users can change the layout of the PivotTable list. They can move the fields that are displayed in the row, column, and data area of the PivotTable list, or add or remove fields from the list. Users can also filter, sort, and group data.

The following illustration shows a PivotTable list on the Orders data access page.



View a datasheet or form in PivotTable view

Access 2002 provides a PivotTable view for datasheets (datasheet: Data from a table, form, query, view, or stored procedure displayed in a row-and-column format.) and forms. In this view, you can build an interactive report using the fields underlying the datasheet or form. The view uses a Microsoft Office PivotTable Component, so you can do all the things that you can do on a PivotTable list. You can add fields to the view, move or remove fields, and filter, sort, and group data.

The following illustration shows the Employees table in PivotTable view.

*************************************************************************
About types of queries
You use queries to view, change, and analyze data in different ways. You can also use them as a source of records for forms, reports, and data access pages (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.). There are several types of queries in Microsoft Access.

Select queries

A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

Parameter queries

A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.)for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.

Parameter queries are also handy when used as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Access prints the appropriate report.

Crosstab queries

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.

Action queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries:

Delete Queries A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.
Update Queries An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

Append Queries An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.

Make-Table Queries A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.)or a history table that contains old records.
SQL queries

An SQL query is a query you create by using an 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.). You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Access.

When you create a query 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.), Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view (SQL view: A window that displays the SQL statement for the current query or is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, Access constructs the SQL equivalent in SQL view. ). If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.

Some SQL queries, called SQL-specific queries (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.), can't be created 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.). For pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.), data-definition (data-definition query: A SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.), and union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), you must create the SQL statements directly in SQL view. For subqueries (subquery: An SQL SELECT statement inside another select or action query.), you enter the SQL in the Field row or the Criteria row of the query design grid.