Building cascading queries
Implement web pages for use through the Internet or the organization's Intranet
Implement cascading queries in an Excel workbook.
Introduction
This section of the web site demonstrates the ease with which one can query data that are stored in a proper format. For a full description of the problem see http://www.mrexcel.com/board2/viewtopic.php?t=199919
Here's a summary of it. This is an organization that has a lot of information stored in files. These files are stored in boxes and the boxes 'belong' to the departments that make up the organization. The issue at hand is how does one find what's in what box? Obviously, the starting point would be a department, and then a box. The person trying to solve this problem was obviously smart enough to know she should check if there was a solution that was better than the one she had been asked to implement.
The correct way to approach this problem is to structure the data into tables that follow the rules of proper database design. Start with two tables, one for the department-box information and another for box-file information.
A big advantage of designing the database properly is that it enables one to ask many more management queries. A few that come to mind include:
- How many boxes do we store on a department-by-department basis?
- What's the average number of files per box across the entire organization?
- What's the average number of files per box on a department-by-department basis?
- And, the all important: Which box contains file XYZ?
The first implementation of the system uses Excel as the database source! While rather unusual and not very recommended, I wanted to demonstrate Excel as a database source.
Each table is in a worksheet as shown below.
| The DeptBox worksheet contains three columns:
|
The BoxDetails worksheet contains four columns. The
design assumes that the same box ID may be used by different departments.
|
For use on a website, the table ranges need names. Use Insert | Name > Define... to set up the names:
| DeptBoxList | =DeptBox!$A:$C |
| BoxFileList | =BoxDetails!$A:$D |
Save this workbook. I called it kristenrice-source.xls.
With this architecture, we can create the query tool using one of two methods. The first would be using Excel itself. The other would be by building a few web pages.
This design, by separating the data storage from the query tool, accomplishes many positive goals, not the least of which is that the data can be moved from an Excel workbook to a 'real' database program such as Access and the query tool design would remain essentially unchanged. All we would have to do is point the tool to that-database rather than this-Excel-file!
We also protect the data and the query engine from user errors. The user interface is simple and easy to use with nothing more than drop down boxes. In addition, users are not inundated with information about every department and every box but can gracefully "cascade" through the organizational heirarchy.
Implement web pages for use through the Internet or the organization's Intranet
The web pages were designed through FrontPage with no HTML level editing or any sort of programming. In fact, with one exception, the design was strictly through the user interface. The one exception was that on the boxlist.asp page I had to add the department ID as a hidden field to the search form.
To see the functional web pages start here.
Implement cascading queries in an Excel workbook.
To see how to implement the queries in Excel start here.