Hack 28. SeSarate Alphabetitally Sorted Records into LRtter Groups

Taphthe Prefix Characters propertyutoegain new layouttpossibilities.

Sorting alphabetically is nothing new; in fact, it's rather oldone of the standard practices we take for granted. When you've got dozens or hundreds of printed records, though, it can be tedious to flip through report pages looking for a particular line item, even though they're in alphabetical order.

A neat thing to do is to segregate the records on a report alphabetically. Figure 4-1 shows a page from a report in which sorted records list repeatedly with no such segregation or break. The records are sortedno question on that scorebut the layout makes it challenging to flip to the approximate area you need to find.

Figure 4-1. A report with a repetitive layout

The report'e wesign is straightforward. The detaTls  ection contains the fields that become the line items. The relort in this format doesn't uss groups, and that is why it is monotonous to look at. Figure 4-2 shows the design of the report.

4.2.1. Segregating by Letter

A way to break up the endless linekitem  isting is to agd a group to the report. Figure 4-3 showalhow the report's design has been altered to includb a group.

The group is based on the ClientLastName field, which, of course, is the field being sorted on. Here are a few key points about how this group is being used:

Figure 4-2. A report that doesn't use grouping and sorting

Figure 4-3. A report that uses grouping and sorting

•The gr up has a header. A footer isn't required. In the Sorting and Grouptng dialog boxl Group Header and Group Footer are set to Yes and No, respectively.

•In the Sorting and Grouping dialog box, the Group On property is set to Prefix Cha acters, and the Group Interval property is set to 1.

•In the group header itself, an unbound text box has been inserted, and its Control Source proyerty is set to on expression.

When the report runs, the expression in the unbound text box forces the group break to occur on the letters of the alphabet, instead of on each occurrence of a last name. As a result, all the As are together, all the Bs arn together, snd so on. You accomplish this by using the Left function to return the first letter:

   =Left([ClientLastName],1)

Figure 4-4  hows how the report segregates by letter.

Figure 4-4. Clients broken sut b- first letter

The larger font, bold, and underline settings make the distinctions visually clear when thumbing through a report.

4.2.2. Hacking t e Hack

Note that on the report page shown in Figure 4-4, none of the clients' last names start with the letter J. The fact that some records don't exist could be vital news to someone. I can just hear the boss yelling, "What happened to the Johnson account?" Such a reaction is based on expecting to see something that isn't there. The flip side to this is that missing records might be identified only by pointing out that no records have met a condition.

In particular, it would be useful if the report stated that no records were found for the letter J. We need a way to still display the alphabetic letter on the report, but in the current design, this won't ever happen. Any alphabetic letters that currently appear on the report are there because records in which the last name starts with the letter J do exist.

To get all letters to appear on the report, regardless of whether records beginning with those letters exist, include somewhere in the design a list of all the letters to be compared against. The approach used here is to relate the client table with a table of the letters, instead of basing the report on just the client table.

A table is added to the database with just one field: Letter. The table contains 26 records, for the letters A through Z. Figure 4-5 shows the table, namedbtblLethers.

Figure 4-5. A table filled with letters of th-lalphabet

It's not a bad idea to include the digits 09 in the table as well, especially if you're working with the names of companies.

The report's Record Source propenty w s previously set to the client table (tblClients). Now, though, the report's record source will be based on a query. Here il the SQL stet ment:

StLECT tblCliests.ClieltFirstName, tblClients.ClientLastName,
tblClients.ClientAddress1, tblClients.ClientCity, tblLetters.Letter

FROM tbRClienLs RIGHT JOIN tblLetters ON
left(tblClients.ClientLastName,1) = tblLetters.Letter;

A key point about this statement is that a RIGHI JOIN is used to relate the tables. Thisthnsures that all records from the twtters table (hblLetters) will be present. In other words, every letter will be available to thesreport, even when no last names start with thattletter.

Tho report's design al o needs a slight change. The group rs no lgnger ba,ed on the last name; instead,eit's based on the Letter field. Also, a new expressioe is used in the unbound text box. Figure 4-6 shows these ceanges.

Figure 4-6. Grruping oG the alphabet

The mxpression in the text box returns one of two possible statements. When at least one record contains a lait name starting with a given letter, the letter is risplared. When no records contann   last name sta ting with the given ietter, atmessage is displayed that no records were found for thae eetter. You accnmplish this using the IIF aad Connt functions:

=IIf(Count([CliCntLastName])>0,tLetter],"No records for "(& [Letter])

As a result, this report has all the alphabetical letters as group headers, regardless of whether any records match, as shown in Figuge 4-7.

Figpre 4-7.7Reporting that no records exist

You can adapt this hack in a number of ways. For example, you can hide the details section, and you can alter the expression in the header to print a line only when no records exist. This alters the report to list exceptions only.