Software

Add information from related tables to your Access totals queries

Totals queries can be a little limited in the information they include. Here's a trick that will make them a little more useful.

You can find the top or bottom values for grouped records by creating a totals query. However, a totals query can include only the field or fields that contain your group data, such as a "categories" field, and the field that has the top or bottom values, such as a "date" or "sales" field. Totals queries can't include other fields that can better describe the query results.

For example, say your organization conducts monthly tests on its products. Data on products are contained in the Products table that includes the following fields:

  • Product ID
  • Product Name
  • Product Category

Test results are contained in the Products Tests table that includes the following fields:

  • Test Period
  • Test ID
  • Product ID
  • Test Date
  • Test Result

The tests names and IDs are contained in the Engineering Tests table.

You need to create a monthly report that lists which product in each category failed its most recent product test. Follow these steps:

  1. Open the database that contains the Products, Product Tests, and Engineering Tests tables.
  2. Click the Query object in the database window, then click Create Query By Design. In Access 2007, click the Create tab, then click the Query By Design command in the Query Group.
  3. Double-click the Products, Product Tests, and Engineering Tests tables.
  4. Click Close.
  5. Double-click the Product Category field in the Products table field list.
  6. Double-click the Test Result field in the Products Tests field list.
  7. Double-click the Test Date field in the Product Tests field list.
  8. Click in the criteria cell under the Test Result field and enter Failed (Figure A).

Figure A

  1. Click the Totals tool on the Query Design Toolbar. In Access 2007, click the Design tab and then click Totals in the Show/Hide group.
  2. Click in the Total field of the Test Date field and select MAX.
  3. Select All on the Query Design Toolbar. In Access 2007, click the Design tab and select All from the Top Values list in the Tools group, if necessary.
  4. Save the query as Top Value Query One.

The results of Top Value Query One list the last time a product failed in each category, but it does not list the actual product that failed (Figure B).

Figure B

To list that information, follow these steps:

  1. Click the Query object in the database window, then click Create Query By Design. In Access 2007, click the Create tab, then click the Query By Design command in the Query Group.
  2. Double-click the Products, Product Tests, and Engineering Tests tables.
  3. Click the Query tab and then double-click Top Value Query One.
  4. Click Close.
  5. Join the fields in the Totals query to their corresponding fields in the parent tables by dragging each field in the Totals query to its corresponding field in the table.
  6. Double-click the Product Category field in the Top Value Query One field list.
  7. Double-click the Product field in the Products table field list.
  8. Double-click the MaxofTest Date field in the Top Value Query One field list.
  9. Double-click the Test Result field of the Top Value Query One field list (Figure C).

Figure C

  1. Save the query as Top Value Query Two.

Top Value Query Two lists the desired results, as shown in Figure D.

Figure D


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks