If you remember, one of the tables within the Ice Cream database is the tblSales table. This contains details of all of the sales (orders) that have been made for the ice creams that our shop produces. As well as detailing who ordered the ice cream, it also stores payment and delivery details. To remind you, here is the structure of the tblSales table.
And here is a diagram of the relationships between the various tables:
As you can see, relationships between the tblSales , tblCompany , tblIceCream , tblIngredients , and tblIceCreamIngredient tables allow us to determine which companies ordered which ice creams and what ingredients were in those ice creams.
Now one of the requirements of our application is that it should allow users to look up previous sales that meet certain conditions or criteria. The criteria identified by the users are as follows :
All sales made to a certain company
All sales of a particular ice cream
All sales of ice creams containing a particular ingredient
All sales of ice creams ordered between certain dates
All sales which it took longer than a certain period of time to fulfill
All sales for which it took the customer longer than a certain period of time to pay
In itself, this might seem fairly daunting, but our users are even more demanding and want to be able to combine any or all of these criteria into a single query. For example, they might want to find out: Which orders for ice creams were made by Jane's Diner in November 2002 where it took more than 7 days for us to fulfill the order?
When they run the query, the users want to know how many orders match the criteria that they have specified. They should then be presented with the opportunity of viewing those results in detail and printing a report containing the matching results.
Now this is not an uncommon type of request. In most database applications there is some type of requirement for flexible querying functionality. One of the characteristics of relational databases is that they divide up complex pieces of information into a number of smaller, related elements. There is more about this characteristic, more formally known as normalization, in the next chapter. The consequence of this division is that it allows us to view our data and analyze our data across multiple dimensions. For example, by division our data into separate tables for sales, ice creams, companies, and ingredients, we have made it a fairly simple task to allow a query to be created which finds the answer to the question posed above. In fact the query required to provide the answer is shown below:
Alternatively, if we wanted to write the query in SQL, we would express it like this:
SELECT tblSales.SalesID FROM tblCompany INNER JOIN tblSales ON tblCompany.CompanyID = tblSales.fkCompanyID WHERE (((tblCompany.CompanyName)="Jane's Diner") AND ((tblSales.DateOrdered)>=#11/01/2002# And (tblSales.DateOrdered)<=#11/30/2002#) AND (([DateDispatched]-[DateOrdered])>7));
Structured Query Language, or SQL, is the language that databases use for manipulating data. Later on in this chapter, we'll look at the basics of how SQL works. If you want to learn more about SQL, why not look at Beginning SQL Programming, from Wrox Press (ISBN 1-861001-80-0)?
Now writing that one query might not seem too complex in itself. But that is just one example of one of the queries that could have been requested . If you cast your mind back to the list of criteria that the users of the application want to search on, we can see that there are 6 of them, any of which can be combined to form a more complex query. Those of you with a mathematical inclination (and a knowledge of Pascal's triangle) will realize that this means that there are 2 6 = 64 different ways in which these criteria can be combined (That's 1 combination involving no criteria, 6 combinations involving 1 criterion, 15 involving 2 criteria, 20 involving 3, 15 involving 4, 10 involving 5 and 1 combination involving all 6 criteria). Pascal's triangle or no Pascal's triangle, that is still a lot of combinations. In fact, when you add to this the fact that each of these criteria can accept different values, the number of combinations becomes truly daunting. So what are we to do?
Our first reaction - and quite a tempting one - is simply to tell our users not to be so demanding. This may annoy our users, so we will assume that we cannot offer this response and that this is the functionality we have to deliver! In any case this is a perfectly reasonable request and exactly the kind of thing you will come across frequently out there in the real world.
One solution might be to devise a parameterized query to solve this problem. To see how this would work, let us assume that we are working with just three possible criteria: Quantity , AmountPaid , and DatePaid . If we were to allow our users to select records based on these criteria, they could combine them in 2 3 = 8 ways.
Now we could cater for all of these combinations with the following query:
The user will be prompted for values for the three different criteria and the query will return the correct values irrespective of which of the criteria the user chooses to fill in. But it is not the most intuitive of queries, especially if you see how the query looks in SQL.
SELECT tblSales.SalesID, tblSales.Quantity, tblSales.AmountPaid, tblSales.DatePaid FROM tblSales WHERE (((tblSales.Quantity)=[Amount Ordered:]) AND ((tblSales.AmountPaid)=[Money Paid:]) AND ((tblSales.DatePaid)=[Payement Date:])) OR ((([Amount Ordered:]) Is Null) AND (([Money Paid:]) Is Null) AND (([Payement Date:]) Is Null)) OR (((tblSales.Quantity)=[Amount Ordered:]) AND ((tblSales.AmountPaid)=[Money Paid:]) AND (([Amount Ordered:]) Is Not Null) AND (([Money Paid:]) Is Not Null) AND (([Payement Date:]) Is Null)) OR (((tblSales.Quantity)=[Amount Ordered:]) AND ((tblSales.DatePaid)=[Payement Date:]) AND (([Amount Ordered:]) Is Not Null) AND (([Money Paid:]) Is Null) AND (([Payement Date:]) Is Null)) OR (((tblSales.AmountPaid)=[Money Paid:]) AND ((tblSales.DatePaid)=[Payement Date:]) AND (([Amount Ordered:]) Is Null) AND (([Money Paid:]) Is Not Null) AND (([Payement Date:]) Is Not Null)) OR (((tblSales.DatePaid)=[Payement Date:]) AND (([Amount Ordered:]) Is Null) AND (([Money Paid:]) Is Null) AND (([Payement Date:]) Is Not Null)) OR (((tblSales.AmountPaid)=[Money Paid:]) AND (([Amount Ordered:]) Is Null) AND (([Money Paid:]) Is Not Null) AND (([Payement Date:]) Is Null));
Now if you scale that up to 6 criteria, then you will find that the query needs 64 criteria rows, with various combinations of Is Null and Is Not Null - and the SQL doesn't even bear thinking about! We think that you will appreciate that this is not a prudent way of implementing the solution.
An alternative (and equally nightmarish) approach would be to create 64 queries and run whichever one corresponded to the particular combination of criteria selected by the user. Trust us, we wouldn't wish the implementation and administration of that solution on anyone . Even minor changes to the database might force us to modify each and every query (without introducing any errors, of course!).
The problem with the first approach, using the parameterized query, is that a very complex query is always run whenever the user asks a question, however simple the question is. Even if the user just wants to find the sales made on 1 st November, the parameterized query approach will always require a beast of a query to be run.
The problem with the second approach is that you have to write and administer 64 separate queries, only one of which is ever used at any one time, irrespective of the questions the user asks. Both of these solutions, then, are very inefficient. What we really want is to have a single query, and for that query to be only as complex as the question posed by the user demands. And to do that we need to modify the query on the fly, in response to the user's selection of criteria. That's what we are going to do in this chapter; we will learn how to programmatically build and use a SQL query string.
By the way, the ability to view the SQL generated by the Query Designer grid in Access is very useful, particularly when dealing with complex joins between tables. Even experienced VBA database developers sometimes use it to design a query. Then they switch to SQL view and copy the SQL it has generated into their VBA code. To do this simply select View menu and then SQL View.And it works just as well the other way. If you are using VBA to build a SQL string, then you can always copy and paste the SQL string into the Query Designer's SQL window. Then you can switch to design view and/or run the query so that you can check that the SQL string really does produce the results you intended.
Our first challenge is working out how we are going to present the user with an intuitive way of selecting one of the 64 different combinations of criteria that are available. One of the best approaches to this problem is to use what is often referred to as a query by form (QBF) interface.
A QBF interface is, at its simplest, just a form containing textboxes or combo boxes for each of the criteria that the user can specify. In our case, there are 6 criteria so the form would look something like this:
As you can see, the user clicks the checkbox to indicate that a particular criterion is to be used. The process of clicking this checkbox enables the text boxes or combo boxes in which the values for the criterion are then entered. The screenshot above illustrates how the form could be used to ask the question we highlighted earlier: Which orders for ice creams were made by Jane's Diner in November 2002 where it took more than 7 days for us to fulfill the order?
In fact, the way the form has been designed, it actually allows substantially more than 64 queries to be produced as the user can specify either a lower or upper limit (or both) for the Order Date and can also select sales with a Payment Delay or Dispatch Delay either more than or less than a certain number of days. And yet this form uses only one query to answer all of those combinations of criteria, and it only takes a few dozen lines of code to make the whole thing work. We'll spend the rest of this chapter implementing this QBF functionality in the Ice Cream database. Once you have seen how it works in this situation you should be able to adapt the ideas that we will be covering to pretty much any situation that calls for QBF functionality. Here's what we are trying to do:
We are going to create the Criteria Form, which will allow us to select our criteria. This will build a SQL query, and this query will be used as the basis for a form or a report.
Before we start to modify any queries we first need to create the QBF form that is shown above. If you want to, you could try your hand at building it yourself, but to make life easier for you, there is a shell criteria form provided in the IceCream07.mdb database.
Open the IceCream.mdb database you have been building as you have been working through the book (or open the IceCream07.mdb database from the CD).
If the Database window is not displayed, make it visible by hitting F11 .
From the File menu, select Get External Data and then Import .
A dialog box will then appear and you will be asked to select the database from which you want to import objects. Select the IceCream07.mdb database and hit the Enter key.
You will then be asked which objects you wish to import. Click the Forms tab and select frmCriteria .
Press the OK button and the frmCriteria form will be imported into the current database.
Now that we have a copy of the frmCriteria form in our database, we can take a look at what happens when the user selects values in it.
Open the version of the IceCream.mdb database you have been using or building.
Open the frmCriteria form.
Check and uncheck the checkboxes on the left hand side of the form. Note how the associated text boxes and combo boxes are enabled and disabled as you check and uncheck the checkboxes.
Check the Company is , Ordered between , and Dispatch delay is checkboxes and enter the following values into the textboxes on the form.
01-Nov-02 and 30-Nov-02
Dispatch delay is
More than 7 days
Now hit Ctrl+G to open the VBA IDE and display the Immediate window.
In the Immediate window, we are going to look at the values held by some of the various combo boxes and text boxes on the form. To find the values they hold, evaluate the following expressions in the Immediate window. To do this, type each of the lines in turn into the Immediate window and hit the Enter key after each.
?forms("frmCriteria")("chkCompanyID") ?forms("frmCriteria")("cboCompanyID") ?forms("frmCriteria")("chkDateOrdered") ?forms("frmCriteria")("txtDateFrom") ?forms("frmCriteria")("txtDateTo") ?forms("frmCriteria")("cboDispatchDelay") ?forms("frmCriteria")("txtDispatchDelay")
You should see the following values returned:
So, although we have still quite a way to go, at least we have a good starting point. We have a form that we can use to gather all of the criteria required to run the various queries that our application needs to be able to cope with. But before we look at how to build the query, let's have a brief look at some of the other functionality in the frmCriteria form.
Firstly, if you want to know how the textboxes and combo boxes are enabled and disabled, just take a peek at the code behind the form (open the form in Design view and then click the Code button or choose Code from the View menu). Each of the checkboxes has an event procedure handling its Click event. For checkboxes, the Click event is not only fired when the user clicks the checkbox with the mouse; it is also fired when the user carries out any action which has the same effect as clicking the checkbox with the mouse, such as pressing the space bar while the checkbox is selected.
Private Sub chkCompanyID_Click() cboCompanyID.Enabled = chkCompanyID End Sub
So what the piece of code above does is to interrogate the value of chkCompanyID . If the checkbox has just been checked, its value will be True ( -1 ); otherwise it will be False . All we then need to do is to assign this value to the Enabled property of any combo boxes or textboxes associated with the checkbox.
The Value property is the default property of a checkbox object, so evaluating chkCompanyID is equivalent to evaluating chkCompanyID.Value .
Secondly, have a look at the values that the textboxes and combo boxes contain. Some of them are obvious, but others bear further investigation. For example, the top three combo boxes return numbers rather than the values that are selected. We do this on purpose as shall become clear. The combo boxes are populated by a query with two columns. One of these columns is the one that is displayed on the form; the other is hidden (by having its Width set to ) and contains the CompanyID for the value being displayed. For example, the cboCompanyID combo box has been populated with the results of this query, which is stored in the Row Source property of the combo box:
So, when Jane's Diner is selected in the combo box, the bound column contains the value 10 , which is what is returned when we interrogate the value of cboCompanyID . Now that's all very interesting, but why bother? To answer that, let us have another look at the SQL that we used earlier in this chapter to evaluate this query:
SELECT tblSales.SalesID FROM tblCompany INNER JOIN tblSales ON tblCompany.CompanyID = tblSales.fkCompanyID WHERE (((tblCompany.CompanyName) = "Jane's Diner") AND ((tblSales.DateOrdered) >= #11/01/02#) AND ((tblSales.DateOrdered) <= #11/30/02#) AND ((tblSales.[DateDispatched] - tblSales.[DateOrdered]) > 7));
You see, the company name is the only part of this query which needs to be retrieved from a table other than the tblSales table. The tblSales table contains the CompanyID , not the CompanyName for the company that bought the ice cream. So, if we wanted to, we could re-write this query like this:
SELECT tblSales.SalesID FROM tblSales WHERE tblSales.fkCompanyID=10 AND tblSales.DateOrdered>=#11/1/02# AND tblSales.DateOrdered<=#11/30/02# AND [DateDispatched]-[DateOrdered]>7
Now rewriting the query in this fashion has two advantages. Firstly, the query should run faster, because there is no need to create a join to a second table. (Indeed if the tables have a lot of data and we have specified a lot of joins then the performance gain may be substantial.) More importantly for us though, the query is much simpler to construct. Remember, we are going to need to be able to create a variety of different queries, depending on what the user selects. The more we can avoid joins, the easier that task will be for us.
The final thing to notice about this form is what happens when we hit the Find Matches button. Have a look at the code in the event handler for the Click event of the cmdFind button and you will see this:
Private Sub cmdFind_Click() Call EntriesValid End Sub
So, when the Click event is fired, this code runs the EntriesValid procedure. As its name suggests, the EntriesValid procedure checks that each of the selections made by the user is valid. So if a user has, for example, checked the " Company is " checkbox, but has not selected a company, the EntriesValid function will display an error message warning the user of this inconsistency.
So, we have a convenient way of collecting input from the user, but how do we go about reflecting the user's selections in the query we will run? In order to do that, we need to know how to manipulate queries. Once we have done that, we will come back and implement that functionality in the frmCriteria form.