Access database queries examples

Tips and Techniques for Queries in Access 2007
July 19, 2022 – 09:39 am
How to Navigate & use the Query by Example (QBE) interface in

Specifying Totals by using the View menu

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

Query with totalsFor 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.

New Query WizardFor 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.

Creating a crosstab query manuallyLeft 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.

Crosstab of Patients and with State name Results of a left join query

outdoor art how the grinch stole christmas coworking porto how to create a business instagram account cake international christmas tree farm near me no time to die box office worldwide subscribers worldwide family christmas pyjamas worldwide logistics coronavirus worldwide society quotes what does a business coach do why might a business want to expand abroad what can i start a business in forbes coworking spaces coworking spaces central london stylish funeral outfits international student bank account uk quest-worldwide christmas cards 2022 furness building society outdoor duo leeds building society redemption statement stylish christmas jumper mens coworking brands interactive model of communication how to get a business phone number amnesty international israel apartheid worldwide disclosure facility hmrc
Related Posts