Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Alphabetized Customer List


If you are new to Microsoft Access Report designing, then this simple Alphabetized Customer List preparation Tutorial will give you enough insight into what it takes to design a simple report.

We will need the following few steps to complete our task:

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

A Sample Report.

Sample alphabetized list of customers Report Preview is given below:

Designing A Report.

Get Some Sample Data.

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

Let us start with importing Customers Table from Northwind.accdb sample database.

  1. Click on External Data Menu.
  2. Click on Access Tool button to display the Import control dialog box, to specify the Source and destination of data.
  3. Click on Browse... button to locate the Northwind.accdb sample database, Select the file and click Open.

    The selected file pathname is inserted into the File Name control on the dialog box.

    The first option is already selected as default, to import one or more required Access Objects from the selected Access database.

  4. Click OK to open the selected Access Database and expose it's Tables, Queries, Reports etc.
  5. Click on the Tables Tab, select the Customers table and click OK to import the selected table.

    Next step is to create a SELECT Query using Customers table as Source.

  6. Click on the Create menu and select Query Design from the Other group.
  7. Click Close button to close the Show Table dialog box, without selecting any object from the displayed list.
  8. You will find the SQL View option at the extreme left of the Tool bar and select it to display the Query's SQL editing window.

    You will find the SQL statement SELECT; in the window.

  9. Copy the SELECT Query Code given below and paste it into the SQL window, over-writing 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];

    As you can see in the above SQL string we have only two columns of data. First column have only one character in all rows, the left-most character taken from the First Name of the customer. The first column name on the query is given as Alpha.

    We have used MS-Access built-in string function Left() for this purpose. There are other useful functions in this category, like Right(), Mid() and others.

    Second Column of data is the name of the Customer, with First Name, Last Name joined together, separated with a space. The second column's name is given as CName. When you use an expression to create the query column data it is better to give a simple name to the column so that it is easy to memorize and use it on the Report or Form.  If you don’t give a suitable name then MS-Access will give names like Expr1, Expr2 (Expression 1, Expression 2 and so on) by default.

    In the ORDER BY clause of the query both the columns are sorted in Ascending (A-Z) Order, first on Alpha column and then on CName column.

  10. Save the Query with the name Customer ListQ.
  11. Open the Customer ListQ in data sheet view and check the data.

    Sample image of what we are going to create is given below for reference:

The Design Task.

Let us design the Report

  1. Select Report Design from the Create menu.

    An empty Report is open in Design View with it’s Property Sheet. The first priority is to define our Customer ListQ Query as the Record Source of our report. If the property sheet is not displayed then click on the Property Sheet tool bar button to display it.

  2. Select the Data Tab on the Property Sheet.
  3. Click on the Record Source property and click on the drop-down list at the right-end of the property.
  4. Find Customer ListQ Query (use the slider, if necessary) and select it from the drop-down list, to insert it into the Record Source property.
  5. Click on Group & Sort Toolbar button from Group & Totals Group under Design Menu, if it is not already in selected state, to display the Group and Sort controls under the Footer of the Report.
  6. Click on the Add a Group control displayed in the Group, Sort and Total shown below the empty report.
  7. Click on the Alpha column name displayed from the Query columns list.

    You can see the Alpha Group Header is now appearing between Page Header and Detail Sections of the empty report.

    We must sort the customer names appearing under a particular character group (say A,B,C... group) so that they will appear in proper order as well. 

    Note: We have sorted the data in the Query by defining ORDER BY clause, but we must define Grouping and Sorting on the Report also to make them appear in proper order on the Report as well.

  8. Click on Add a Sort control and select CName from the list.

    Now, let us create the Heading of the Report, Group Heading (A, B, C... etc.) and customer names list to appear under each group.

  9. Click on Label control to select it and draw a rectangle, wide enough to write the heading text "CUSTOMER LIST", select Bold and Italic formatting styles and change the font size to 16.
  10. Select the Text Box control and draw a text box on the Alpha Header Section of the report.
  11. Click on 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.
  12. Select the child lable of the text box and delete it.
  13. Create another text box in the Detail Section of the Report, below the Alpha Header control.
  14. Select CName column name from the drop-down list in the Control Source property under Data Tab on the property sheet of the text control.
  15. Reduce the Detail Sectoin height by dragging the Page-Footer section bar up to touch the text box's edge.
  16. Save the report with the name: Customer List.

    Print Preview the Report.

  17. Open the Customer List report in print preview and see how it looks.

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


No comments:

Post a Comment

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Key Report msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Item Macros Menus RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email msaccess menus progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers ImageList Control Import Labels List ListView Control Logo Macro Mail Merge Main Form Memo Monitoring Nodes Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page TreeView Control Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code