Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Drill-Down Inquiry Screen

Introduction

Following is an image of a three-layer Inquiry Screen, designed for the Management, to keep track of the Northwind Traders' staff-level Sales Performance:

The top layer of the Form shows the Salesperson level summary of Orders and the Percentage of Total Orders. The Footer of the Form shows the Total of all Orders put together. When clicked on a particular Employee's record the individual Order-level Summary information will be visible on the main screen overlaying the earlier view. Check the image given below.

Several items can be ordered under a particular Order and details of all items can be displayed by clicking on one of the Order records. Check the image given below.

The above Form shows order-level details and summary information at the Footer of the form with the Total Quantity of all Orders and the Total Net Value of all items after discount. The Command Buttons at the footer of each form are used to switch the view back to the upper layer of the form.

The Date Parameter values at the top can be changed to display information for a different period.

First of all, I would like to assure you that there is no complicated VBA Code to drive this design except one or two lines of them here and there to refresh the controls and for switching from one layer of the form to the other. Other than that, the entire Screen works on Queries and Forms.

Designing the Forms

We need a few Tables from the Northwind.mdb sample database, to create six simple Queries, three Sub-Forms, and the Main Form to organize all of them together to present the information to the User.

Note: If you would like to see the Inquiry Screen in action before you design it, you can download it from the bottom of this post and perform a demo run. If you find it difficult to figure out how it works or how to put all of them together, then come back and go through the design task step by step so that you will know how simple or difficult it is and how each element of this trick works together with others.

Here, we will see the usage of Transparent Command Buttons again, which we have already seen in an earlier article with the same title.

We will have to split this topic into more than one Blogpost so that we can do the design task with ease. Even though I can show you how to do it in a few minutes, explaining that on paper with images and Property settings takes a lot more than that.

I know that, if you download the database and take a look at it first, you will be better prepared to find out more about it and ready to go through the design task with more interest.

The Design Task

So, let us start with the first part.

  1. Download the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MSOffice2003 address, you can replace Office in place of Office11 if your Version of Access is 2000):
    • Employees
    • Orders
    • Order_Details
    • Products

    A Parameter Table.

  2. Create a Parameter Table with the following name and Structure:
    Parameter Table: Date_Param
    Srl. Field Name Data Type Size
    1. StartDate Date/Time  
    2. EndDate Date/Time  
  3. Open the Table and create a single record with the following StartDate and EndDate values and save the record:
    Parameter Table: Date_Param
    StartDate EndDate
    01-01-1996 31-12-1996
  4. Open new Queries and Copy the following SQL Strings into Query's SQL Editing Window and save them with the exact Name given for each of them. Create the Queries in the same order as given here as they have dependencies on Queries created first.
  5. Query Name: 00_Orders_MainQ
    SELECT Orders.*
    FROM Orders, Date_Param
    WHERE (((Orders.OrderDate) Between [StartDate] And [EndDate]));
    
  6. Query Name: 01_OrderCount1
    SELECT Employees.EmployeeID,
      [FirstName] & " " & [LastName] AS EmpName,
       Count([00_Orders_MainQ].OrderID) AS TORDERS
    FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
    GROUP BY Employees.EmployeeID, [FirstName] & " " & [LastName];
    
  7. Query Name: 02_OrderCount2
    SELECT Count([00_Orders_MainQ].OrderID) AS TOTALORDERS
    FROM 00_Orders_MainQ;
    
  8. Query Name: 03_Employee_Summary
    SELECT [01_OrderCount1].*,
          [TORDERS]/[TOTALORDERS] AS PCNT
    FROM 01_OrderCount1, 02_OrderCount2;
    
  9. Query Name: 04_Order_ListQ
    SELECT [00_Orders_MainQ].OrderID,
            UCase([FirstName] & " " & [Lastname]) AS EmpName,
           [00_Orders_MainQ].OrderDate,
            [00_Orders_MainQ].RequiredDate
    FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
    WHERE ((([00_Orders_MainQ].EmployeeID)=[Forms]![Inquiry_Main]![EID]));
    
  10. Query Name: 05_Order_DetailQ
SELECT [FirstName] & " " & [LastName] AS EmpName,
        [Order Details].OrderID,
        [Order Details].ProductID,
        [Order Details].Quantity,
        [Order Details].UnitPrice,
        [Order Details].Discount,
        (1-[Discount])*[UnitPrice]*[Quantity] AS ExtendedPrice
FROM (Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID) INNER JOIN [Order Details] ON [00_Orders_MainQ].OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID)=[Forms]![Inquiry_Main]![OID]));

Continued Next Week.

Download


Download Demo InquiryDemo00.zip


  1. Multiple Parameters for Query
  2. Form Menu Bars and Toolbars
  3. Seriality Control - Missing Numbers
  4. Wave Shaped Reminder Ticker
  5. No Data and Report Error
Share:

3 comments:

  1. The Parameter Table Name is Date_Param

    The omission is regreted.

    Regards,

    ReplyDelete
  2. I’ve recently started a blog, the information you provide on this site has helped me tremendously. Thank you for all of your time & work.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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