Access database queries examples
Total Queries
Up to now, we have only retrieved records. With lots of data, it is important to calculate summaries for groups of records or totals on the entire table. This is possible by specifying Totals from the Show/Hide ribbon in Access 2007…
Figure 8. Specifying Totals from the Ribbon
…or by using the View menu in Access 2003 or earlier.
Figure 9. Specifying Totals by using the View menu
This performs calculations across all the records and creates a summary result. For example, you can Sum on a numeric field to determine the total for the entire table. Additionally, you can group on fields to calculate totals for each unique combination of values across the group fields.
When Totals is selected, a new Total row appears in the query design. You can specify the type of calculation you want in that section.
Figure 10. Query with totals
For this query, the result shows average Age, Weight and Cholesterol for patients by State and Gender.
Crosstab Queries
Crosstabs are a powerful analysis tool that lets you quickly see the relationship of data between two fields. The view is a spreadsheet-like display with unique values of one field as rows, unique values of another field as columns, and the summary of another field as the cells in the matrix.
For example, with the previous example, a crosstab can clearly show the average Cholesterol between State (rows) and Sex (columns).
Figure 11. Viewing crosstab results
The easiest way to create a crosstab is to use the Crosstab Wizard. When creating a new query, select Query Wizard and then follow the Crosstab Query steps.
Figure 12. New Query Wizard
Crosstab queries can also be manually created by selecting Crosstab from the Query menu and specifying the Row and Column Headings.
Figure 13. Creating a crosstab query manually
Multi-table Queries
To this point, all the queries shown were for one table only. Microsoft Access queries allow very sophisticated multi-table queries. Criteria and field selections can be from any of the query's tables. Linking tables on fields is done visually by dragging a line between the fields to link.
For our previous example, we might want to show the full name of each state instead of its abbreviation. With a State table that contains the abbreviation and full names, this can be easily performed.
Figure 14. Crosstab of Patients and with State name
Notice the link on the [State] fields and the [Name] field from the States table in the query. To create multi-table queries, the Table row should be displayed. This can be activated from the View | Table Names menu. Even better, the default query options should set Show Table Names to Yes.
There are several ways to join tables in a query. The previous example was the most common which is an exact match between fields, sometimes called an inner join. Another join includes retrieving all records from one table regardless of whether there are matches in the second table. This is called a left join. If the logic is reversed (all records from the second table and matching records from the first) it is called a right join. These options can be selected by double-clicking on the linking line and choose among the three options.
Left Join Between Tables
Here is an example of a query with a left join and the results.
Figure 15. A left join query
Notice how States that do not have patient data are shown with no value in the MaxOfAge and AvgOfCholesterol fields.
Figure 16. Results of a left join query
No Joins Between Tables
Queries with multiple tables do not even require a line between the tables. If no lines are specified, a record by record link is assumed. That is every record in the first table is linked to every record in the second table. This is most useful when one of the tables only has one record. Finally, tables can be linked through an expression that establishes a partial match or match based on a range of values. Examples are shown later.
Basing Queries on Other Queries
So far, the queries presented are only based on tables. However, Microsoft Access lets you also base queries on other queries. This ability to filter and analyze data across several levels is extremely powerful. The queries themselves behave identically whether the data comes from tables or queries.
Basing queries on other queries can also simplify maintenance of your database by letting you create standard queries that other queries can use. This can be particularly useful in reports. Of course, you need to be careful modifying the "core" queries.
Additionally, when you generate reports in multi-user databases, make sure that you don't use queries when you should use temporary tables that are generated by Make Table and Append queries. Queries always retrieve the most current data. If you are printing many reports while others are modifying the data, and consistency between reports is important (the numbers need to tie), you must create temporary tables with the data you need prior to printing. You can then base your queries on those "static" tables.
