Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Highlighting Reports


Analysis of data stored in Tables and providing resultant information in the form of Reports to the outside world is an important function of any Database Management System.

An Access Application may contain several Tables logically related but maintained separately for easier handling. When it is time to prepare Reports related tables are selected and joined on common information in them, filtered and placed the output on Reports. Meaningful and timely Reports play an important roll in decision making.

Report designing is an Art in its own merit, like Form designing. MS-Access is equipped with excellent Report Designing Tools and with little practice you can master it.

We have been asked to prepare a report on the Company's Sales Team, to review their monthly Sales Target Achievement. A mandatory Target Amount of $100,000 is fixed on each member of the Team and the Management wants a month-end Report to review his or her performance. The management focuses on the performance of the Sales Team every month and if any member of the Team lags behind the Target, they have to undergo a postmortem of their activity area to set things right for the future.

We have prepared a sample Report on the Sales Team's Month-end figures, highlighting cases that didn't meet the stipulated Target Amount of $100,000, with a red circle around them.

The finished Report image is given below:

The tabular Report above is designed with the Report Wizard and nothing special about it except highlighting of Values less than $100,000 with a circle around them. A small VB Routine in the Detail_Print() Event Procedure compares value on each row with the target figure of $100,000 when the printing/print previewing is in progress and if any of them is less than $100,000 then draws a red circle around that Value.

Prepare the Report

To prepare this report we need two Tables from the Northwind.mdb database. If you are not sure where the file can be located, visit the earlier Post: Saving Data on Forms not in Table for the location reference of the Northwind.mdb database. Import the following two Tables from the Northwind.mdb database.

  • Orders
  • Order Details
  1. Click on the Query Tab in the Database Window.
  2. Click New to create a new Query and click Close button on the Show Table dialog control to close it without selecting a Table from the list.
  3. Click on the Toolbar button with the SQL label on it to display the Query's SQL Window.
  4. Copy and paste the following SQL String into the SQL window and save it with the Query Name: SalesReportQ0.
    SELECT Orders.OrderID,
     [Order Details].UnitPrice,
     [Order Details].Quantity,
     [UnitPrice]*[Quantity] AS Extended_Price
    FROM Orders 
    INNER JOIN [Order Details] 
    ON Orders.OrderID = [Order Details].OrderID;
  5. Go through steps 2 & 3 above, to create another Query from the source data of the first Query SalesReportQ0, Copy and Paste the following SQL String into the Query's SQL Window and save it with the Name:SalesReportQ
    SELECT SalesReportQ0.EmployeeID,
     Sum(SalesReportQ0.Extended_Price) AS Total
    FROM SalesReportQ0
    GROUP BY SalesReportQ0.EmployeeID;
  6. Click on the SalesReportQ Query and select Report from the Insert Menu. Select Auto Report: Tabular on the Report Wizard and make changes to the design to look like the sample image given below:
  7. Click on the Total Field and display the Property Sheet. Change the following Property Values:
    • Name : Total
    • Border Color : 12632256
  8. Display the VB Module of the Report, click on the Code Toolbar Button above or select Code from View Menu.
  9. Copy and paste the following Code into the VB Module of the Report:
    'Global declaration. 
    'The next line (Dim MinTarget As Double,  curVal As Double) 
    'must be placed just below the Option Compare Database line 
    ' (and Option Explicit line, if present) at top of the Form Module.
    Dim MinTarget As Double, curVal As Double
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    '  Draw ellipse around controls that meet specified criteria.
    Dim ctl As Control, ctl2 As Control, intPrintCircle As Boolean
    Dim sngAspect As Single, sngYOffset As Single
    Dim intEllipseHeight As Integer, intEllipseWidth As Integer
    Dim sngXCoord As Single, sngYCoord As Single
    On Error GoTo Detail_Print_Err
    MinTarget = 100000
    curVal = Me![Total]
    If curVal < MinTarget Then
      intPrintCircle = True
      intPrintCircle = False
    End If
    'slightly increase/decrease this value 'to adjust the oval shape.
      sngAspect = 0.3
    'Determine height and width of ellipse.
     intEllipseHeight = Me.Total.Height
      intEllipseWidth = Me.Total.Width
      sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)
      Set ctl2 = Me.Total
        If Not IsNull(ctl2) Then
            Set ctl = ctl2
     'Determine coordinates of ctl and draw ellipse.
        If intPrintCircle Then
          sngXCoord = ctl.Left + (intEllipseWidth \ 2)
          Me.Circle (sngXCoord, sngYCoord), intEllipseWidth \ 2, RGB(255, 0, 0), , , sngAspect
          intPrintCircle = False
        End If  
    End If
    Exit Sub
    MsgBox Err.Description, , "Detail_Print"
    Resume Detail_Print_Exit
    End Sub
  10. Save the report with the Name: Sales Report.
  11. Open the report in Print Preview to see how it works. The Circle is drawn based on the size of the Total Textbox. If the control is too wide then upper and lower parts of the elliptical shape may not appear within the control boundary. To correct this problem you can reduce the width of the Total Field, if it is too wide, or you may reduce the value in sngAspect = 0.3 in the program into say 0.25. You may increase or decrease this value and try out to perfectly fit the elliptical shape within the textbox boundaries.

Conditional Formatting

If you prefer Color Coding the Sales Values, rather than struggling with the above technique, then there is an easier way to do it known as Conditional Formatting (MS-Office 2000 or later).

Let us say, we want all values above $100,000 should appear in Green Color, below $100,000 in Red and all $100,000s should print in normal Color. Sample image is given below:

  1. Make a copy of the Sales Report and delete the Code from the VB Module.
  2. Click on the Total textbox in Design View.
  3. Select Conditional Formatting. . . from the Format Menu.
  4. Set the conditions and the Color as shown below:
  5. After setting the first condition click Add>> to expand the control down to set more conditions. Up to three conditions you can set on the same field with different Formats. After setting the values click OK to save the changes.
  6. Open the Report in Print Preview to display the Values in different colors based on the conditions that we set on the control.

When Conditional Formatting not feasible

There are situations that we cannot use the conditional formatting method and need to go by the drawing the elliptical shape method. In both examples given above we are comparing individual values to a constant ($100,000) and then draws a circle or changes color with conditional formatting.

Assume that we are running a Service Station and we have Service Contracts with Companies for servicing their vehicles regularly for a certain period of time or the Vehicle's Odometer reading reaches a certain level. Till that time the Vehicle comes for servicing on a fixed interval of 3 months or after every 5000 KMs etc.

The management asks us to prepare a Report on all Vehicles on Service Contracts showing Date of Service, Odometer Reading & the Expenses. The Report is sorted on Service Date and if there is any Odometer recording found in Error (i.e. the meter reading is recorded less than the earlier service-time recording) that must be highlighted on the Report to quickly identify them for investigation. It can be tampering of the meter or it can be recorded wrongly by the Servicing personnel. In either case it should be highlighted.

In this case, we have to compare each value with the previous meter reading and if the current value is found less than the previous one then it must be highlighted with a circle. There may be repetition of lesser values further then we have to continue putting circles around all those values till we reach a value greater than the one that we are holding as key. In these kind of situations the conditional formatting method cannot be used.


Download Demo Database


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts