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:

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

The top section of the Form displays a salesperson-level summary of orders along with the percentage of total orders. The Form footer shows the combined total of all orders. When you click on a specific employee's record, the individual order-level summary for that employee will appear on the main screen, overlaying the previous summary view. Refer to the image below for illustration.

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

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.

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

The Form displays order-level details along with summary information in the footer, showing the total quantity of all orders and the total net value of all items after discounts. The command buttons at the footer of each sub-form allow you to switch back to the upper layer of the Form.

The date parameter values at the top can be adjusted to display information for a different period.

I want to assure you that there is no complex VBA code driving this design—only one or two lines are used here and there to refresh controls or switch between form layers. Beyond that, the entire screen operates using Queries and Forms.

Designing the Forms

We need several tables from the Northwind.mdb sample database to create six simple queries, three subforms, and a main form to organize them and present the information effectively to the user.

Note: If you would like to see the Inquiry Screen in action before designing it, you can download the demo at the bottom of this post. If you find it difficult to understand how it works or how to assemble all the components, you can return here and follow the step-by-step design instructions. This will help you see how simple or complex it is and understand how each element interacts with the others.

In this tutorial, we will also revisit the use of transparent command buttons, which were covered in an earlier article with the same title.

Due to the complexity of the design, this topic will be split across multiple posts. Although I could show you how to build it in just a few minutes, explaining the process with images and property settings requires more detail.

Downloading and exploring the demo database first will help you better understand the functionality and make the step-by-step design task more engaging.

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 the queries' SQL Editing Module surface, and save them with the exact Name given for each one of them. Create the Queries in the same order as shown below, 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