Avoid inefficient Access reports by sorting months chronologically

 

by Christine Spencer

Application: Microsoft Access 2000/2002/2003/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201803/sortmonth.zip

Access sorts month names alphabetically, since it doesn’t recognize them as dates—and it’s difficult to navigate and analyze data over time when the report isn’t in chronological order. To chronologically sort by month, we’ll show you how to use a query to get the data you need and sort the report the way you want it.

To sidestep this sorting problem and sort our data in chronological order, we’ll:

  • Recognize the problem causing an alphabetical month sort in a report.
  • Build a query that combines a month number with the other data we need.
  • Use the query data in an existing report to sort the report properly.

When you store only the month name instead of an actual date in your table, your reports display an alphabetical month order (April, August, February, etc.) as opposed to a chronological one. This is because the month name is simply text. A date is actually a serial number, so when Access sorts dates, the order is chronological. Fortunately, you can work around this sorting problem with a table that associates the month name with a value, and then uses a simple query to run the report with a sort order that reflects the calendar year.

Examine a report sorted alphabetically by month

First, let’s look at a report that sorts the data by month alphabetically.

  1. Download and extract the file sortmonth.zip from the URL listed at the beginning of this article and open the file Expenses.mdb. If necessary, display the report objects by clicking Reports in the Objects pane.
  2. Double-click on the report named rptExpense Checks By Month to open it. As shown in Figure A, the report sorts the checks by month—but not chronologically. This is because only the month name is stored in the table as a text value.
  3. Close the report.

IME18306a

A: This report sorts the months alphabetically because the month information is stored as a text value.

Create a query to allow for a chronological sort

To create the report we want, we need to sort by the month number—not by the month name. Fortunately, this database has a table, called tblMonths, which contains the month names and numbers. We’ll use that table with the check data to sort the report. To combine the fields we need, we’ll build a select query and then use the month number in the query to sort the report correctly. Since we know which tables and fields we need, we’ll start building the query from scratch in Design view instead of using the Simple Query Wizard.

 

To add tables to a new query:

  1. Display the query objects by clicking Queries in the Objects pane.
  2. Double-click on Create Query In Design View in the Database window to open a blank query design grid and the Show Table dialog box.
  3. Add the table tblEngineer Names to the query by double-clicking on it in the Show Table dialog box.
  4. Include the tables tblExpense Checks and tblMonths in the query by double-clicking on them.
  5. Click Close to dismiss the Show Table dialog box.

 

Add fields to the query

Only the fields included in the query are available to the report. Next, we’ll add any fields to the query that we need for our report. To ensure that we have every field we need from the tblMonths, tblEngineer Names, and tblExpense Checks tables, we’ll use the asterisk (*) at the top of the lists of table fields.

To design your report from various table fields:

  1. Drag the field name MonthNum from the tblMonths field list to the next blank cell in the Field row. (We don’t need to add the month name field from this table because we’ll include it in the tblExpense Checks table.)
  2. Fro[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage