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, 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 role 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 a 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 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 to highlight Values less than $100,000 with a circle around them. A small VB Routine in the Detail_Print() Event Procedure compares the 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 the 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 a 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:
    Option Compare Database
    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, mNewSpeedo As Long
    On Error GoTo Detail_Print_Err
    MinTarget = 100000
    curVal = Me![Total]
    If curVal < MinTarget Then
      intPrintCircle = True
      intPrintCircle = False
    End If
        Set ctl2 = Me.Total
    If Not IsNull(ctl2) Then
        Set ctl = ctl2
        If intPrintCircle Then
       ' change this value to adjust the oval shape of the circle.
        sngAspect = 0.25 'Increase/Decrease this value to adjust the shape
        ' Get Height and Width of Control
        intEllipseHeight = Me.Total.Height
        intEllipseWidth = Me.Total.Width
        'Calculate Center X,Y Corordinate
        sngXCoord = ctl.Left + (intEllipseWidth \ 2)
        sngYCoord = Me.Total.Top + (intEllipseHeight \ 2)
        ' Draw Ellipse within the Control
                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, to 0.25. You may increase or decrease this value and try out to perfectly fit the elliptical shape, within the textbox boundary.

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 Red, and all $100,000s should print in normal Color. A 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 is not feasible

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

Assume that we are running a Service Station and we have Service Contracts with Companies 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 to servicing on a fixed interval of 3 months or after every 5000 kilometers. etc.

The management asks us to prepare a Report on all Vehicles on Service Contracts showing the Date of Service, Odometer Reading & 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 in the Report to quickly identify them for investigation. It can be tampering with 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 a repetition of lesser values than 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 this kind of situation, the conditional formatting method cannot be used.



No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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 Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript 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 Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page 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