Grouping by the first letter of a company name

You might want to break your data into groups based on the first letter of the company name. For example, in a customer list you might want all the "A" customers in a group, then all the "B" customers, and so forth. To do this requires the use of a formula.

Do not worry if you are unfamiliar with formulas. This text will show you what formula you need here and how to enter it.

You can learn more about creating and editing formulas in Formulas overview.

You are going to create a formula that will extract the first letter of each customer's name. Then you will group the data using that formula field as the sort-and-group-by field. The program will sort the data based on the first letter of each customer name and start a new group whenever that letter changes.

To group data by the first letter of a company name

1. Create a report using the sample data, Xtreme.mdb, and place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME}
{customer.REGION}
{customer.POSTAL CODE}
{customer.COUNTRY}

1. On the Insert menu, click Formula Field.

   The Field Explorer dialog box appears.

   

2. Double click Formula Fields.
3. In the Formula Name dialog box, enter the name you want to identify the formula, for example "First Letter."
4. Click OK.

   The Formula Editor appears.

5. Choose Crystal syntax from the drop down list in the top right corner.
6. Type the following formula into the Formula text box:

{Customer.Customer Name}[1]

7. Click the Save and Close button.

   You return to the Field Explorer dialog box. The name of the formula is listed in the Formula Fields directory.

8. On the Insert menu, click Group.

   The Insert Group dialog box appears.

   

9. Select the formula field as the field you want your data grouped by from the first drop-down list.
10. Select the sort direction from the second drop-down list.
11. Click OK.

    You return to your report with the data grouped by the formula field as specified. The data is broken into groups based on the first letter in the customer's name. The formula provides a live header for every group.

    For more information on live headers, see Live headers.

    Your report should look similar to the following:

    

Related topics

Formulas overview.

Grouping data.

Grouping data in intervals.

Creating group headers.