Alphabetized Customer List ~ LEARN MS-ACCESS TIPS AND TRICKS

Alphabetized Customer List.

If you are new to Microsoft Access report design, this simple tutorial on creating an alphabetized customer list will give you a clear understanding of the basics. It provides valuable insight into the steps involved in designing a report. We will need the following steps to complete our task:

Prepare the Customers' source data in a SELECT Query for the report.
Open a new report in Design View.
Insert the SELECT Query name into the Record Source Property of the Report.
Use the Data Grouping and Sorting option of the Report to organize and display A, B, C, etc., in the Group Header.
Design the Report.
Preview the Report.

A Sample Report.

Sample alphabetized list of customers. Report Preview is given below:

Right-click to open Large Image in New Tab/Window.

Designing A Report.

Get Some Sample Data.

But first, we need some ready-made sample data for our Report

Let us start by importing the Customers Table from the Northwind sample database.

Click on the External Data Menu.
Click the Access Tool button to display the Import control dialog box to specify the Source and destination of data.

Right-click to open Large Image in New Tab/Window.
Click on the Browse... button to locate the Northwind sample database, select the file, and click Open.

The selected file pathname is inserted into the File Name control in the dialog box.
The first option is already selected as the default to import one or more required Access Objects from the selected Access database.
Click OK to open the selected Access Database and expose its Tables, Queries, Reports, etc.
Click the Tables tab, select the Customers table, and click Ok to import the selected table.

The next step is to create a SELECT Query using the Customers table as the Source.
Click on the Create menu and select Query Design from the Other group.
Click the Close button to close the Show Table Dialog Box without selecting any object from the displayed list.
You will find the SQL View option on the left of the Toolbar; select it to display the Query's SQL editing window.

You will find the SQL statement SELECT in the window.
Copy the SELECT Query Code given below and paste it into the SQL window, overwriting the existing SELECT statement.
```
SELECT Left([First Name],1) AS Alpha, [First Name] & " " & [Last Name] AS CName
FROM Customers
ORDER BY Left([First Name],1), [First Name] & " " & [Last Name];
```
In the SQL string shown above, we are working with only two columns of data. The first column, named Alpha, contains a single character from each row—the leftmost character of the customer’s first name—extracted using Microsoft Access’s built-in String function Left(). Access also provides other useful string functions in this category, such as Right(), Mid(), and more.
The second column, named CName, contains the customer’s full name, created by joining the first and last names together with a space in between. When building query expressions like this, it is always good practice to assign simple, meaningful names to the calculated columns. This makes it much easier to remember and reference them later in reports or forms. If you don’t provide explicit names, Access will automatically assign generic names such as Expr1, Expr2, and so on, which can be confusing when working with queries.
In the ORDER BY clause of the query, both columns are sorted in ascending (A-Z) order, first the Alpha column, then the CName column.
Save the Query named Customer ListQ.
Open the Customer ListQ in the datasheet view and check the data.

A sample image of what we are going to create is given below for reference:
Right-click to open Large Image in New Tab/Window.

The Design Task.

Let us design the Report.

Select Report Design from the Create menu.
An empty Report is open in Design View, with its Property Sheet. The first priority is to define the CustomerListQ Query as the Record Source of our report. If the Property Sheet is not displayed, then click on the Property Sheet toolbar button to display it.
Select the Data Tab on the Property Sheet.
Click the Record Source Property, and click the drop-down list at the right end of the property.
Find CustomerListQ Query (use the slider, if necessary) and select it from the drop-down list to insert it into the Record Source property.
Click the Group & Sort Toolbar button in the Group & Totals Group under the Design Menu, if it is not in the selected state, to display the Group and Sort controls under the Report Footer Section.
Click the Add a Group control displayed in the Group, Sort, and Total shown below the empty report.
Click on the Alpha column name displayed in the Query columns list.

You can see that the Alpha Group Header is between the Page Header and Detail Sections of the empty report.
We must sort the customer names by their first character (A, B, C order) so that all names appear under the first Alpha Character.
Note: All Names starting with the letter A will appear under Group A, all names starting with the letter B will list on the Report under Group B, and so on.
Click Add a Sort control and select CName from the list.

Now, let us create the Report Heading, Group headings (A, B, C, and so on), and customer names to appear under each group.
Click the Label control to select it, draw a rectangle for the Heading Text "CUSTOMER LIST", select Bold and Italic formatting styles, and set the font size to 16.
Select the TextBox control and draw a text box on the Alpha Header Section of the report.
Click the Data Tab on the Property Sheet and select Alpha from the Control Source drop-down list. Change the font style to Bold and character size to 16.
Select the child label of the text box and delete it.
Create another text box in the Report Detail Section, below the Alpha Header control.
Select the CName Column name from the drop-down list in the Control Source property under the Data Tab in the TextBox Property Sheet.
Reduce the Detail Section height by dragging the Page-Footer section to the height of the TextBox.
Save the Report as Customer List.

Print Preview the Report.
Open the Customer List report in Print Preview to view.

If the Heading, Group heading, and customer list are not properly aligned to the left in your report, as shown in the first image at the top, align all the controls to the left.