Smart Selection Criteria For Queries Using Combo Boxes

by Nirmala Sekhar from saicomsystems.com

Everyone builds databases to store data. Right? 

Wrong! The primary aim of an efficient database is to retrieve information out of all the data that has been entered in the database. In this issue, we take a look at how we can use forms to specify the criteria for building queries. These queries can be used on their own or as the basis of different reports. 

Those familiar with the Northwind database will immediately recognise that I have pilfered a few tables, queries and reports from the database for this example. 

Adding "ALL" to a combo box using a Union Query 

When users select a category or a product, it is always nice to provide them the option of selecting "ALL categories". The mechanism to use is called a Union Query. 

A union query is used to merge the results of two or more queries, tables or SELECT statements, in any combination. Some of the aspects of union queries that you need to remember are: 

The union query can only be viewed and designed in SQL view. The query grid view is not available for this query. Hence, you need to be familiar with SQL if you need to use this. 

All the different queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type. 
Use aliases only in the first SELECT statement because they are ignored in any others. 

You can use a GROUP BY or HAVING clause in each query argument to group the returned data. 

You can use an ORDER BY clause at the end of the last query argument to display the returned data in a specified order. However, remember to refer to the ORDER By fields by what they are called in the first Select statement. 
By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster. 

Now, let us see how we can use a Union query to create a combo box that will display all available categories plus a separate selection to indicate "All Categories". The combo box will be as follows: 

Column Count    : 2
Bound Column    : 1
Default Value   : "*"
Row Source Type : Table/Query
Row Source   :
       SELECT "*", "<< All Categories >>" As CatName 
       From Categories  
       UNION 
       (Select CategoryID, CategoryName FROM Categories;) 
       Order By CatName;        

Notes: 
CategoryID is actually a numeric field but we have defined it's union with a string value "*". The reason for that will become clear later on, as we define the queries. 
The alias CatName has been defined in the first SELECT statement and this has been used in the ORDER BY statement. If we try to use the CategoryName for sorting, it will not work. 

Since we are sorting by category name, the text we have used for "<< All Categories >>" begins with a character that will guarantee that it will be the first in alphanumeric sort. The value you use in your own databases has to be based on your understanding of data in the actual table. 
The Employees combo box is slightly different in that it combines two fields from the table. 

Row Source   :
            SELECT "*", "<< All Employees >>"  
            From Employees  
            UNION 
            (Select EmployeeID, [LastName] & 
            (", " + [FirstName]) FROM Employees;);

Cascading Combos 

The next challenge is to define the combo box for Products which will be based on the category selected in the Categories combo box. The initial values for the combo box will be as follows: 

Column Count    : 3
Bound Column    : 1
Default Value   : "*"
Row Source Type : Table/Query
Row Source   :
    SELECT "*", "<< All Products in all categories >>" 
    As Prodname, "*" As CatID 
    From Products  
    UNION 
    (Select ProductID, ProductName, 
    CategoryID FROM Products;) 
    ORDER BY ProdName;    

The initial default value for the Categories combo box is "*" or "All Categories" so the above union query will be fine. Once the user changes the selection in the Categories combo box, the above row source has to be changed. This is done in the AfterUpdate event using the following code: 

Private Sub cboCategory_AfterUpdate()
Const strQ As String = """"

If cboCategory.Column(0) <> "*" Then
   ' User has selected a specific catagory

   cboProduct.RowSource = "SELECT " & strQ & "*" & strQ _
       & ", " & strQ & "<< All Products in Category >>" _
       & strQ & " As Prodname, 0 As CatID " _
       & " From Products  UNION " _
       & "(Select ProductID, ProductName, " _
       & " CAtegoryID FROM Products " _
       & " Where CategoryID = " _
       & Forms!frmselection!cboCategory & ";) " _
       & " ORDER BY ProdName;"
Else
   cboProduct.RowSource = "SELECT " & strQ & "*" _
       & strQ & ", " & strQ _
       & "<< All Products in All Categories >>" _
       & strQ & " As Prodname, 0 As CatID " _
       & " From Products  UNION " _
       & "(Select ProductID, ProductName, " _
       & " CAtegoryID FROM Products ;) " _
       & " ORDER BY ProdName;"
End If
' Requery the combo box
 cboProduct.Requery
'Reset the value in Products combo box
 cboProduct = "*" 
End Sub

Building the queries 

Once we have the forms with the combo boxes, query definition based on them is fairly easy. All you need to do is to use is the Like operator in the Criteria grid for the specific column. If you see Help on Like operator, you will find that "*" is used with Like to match zero or more characters. 

In our example, let us match EmployeeID and CustomerID using this snippet of SQL 

WHERE (((Employees.EmployeeID) Like [forms]![frmselection]![CboEmployee]) 
AND ((Orders.CustomerID) Like [forms]![frmselection]![CboCustomer]))

Now, you will begin to realise, why we used "*" for the union query. IF the Employees combo box has "*", then the above query will pick up all employees. On the other hand, when the user selects a particular employee, only records for that particular employee will be picked up. 

--------------------------------------------------------------------------------
You can use the above concepts for queries that feed forms, reports and charts. The sample database includes sample queries and reports that are based on the queries.

Click Here  to download the sample database. (Zip file 325 kb)

Author Bio:

Nirmala Sekhar is a software consultant working from Singapore. Her company - Saicom Systems, publishes a monthly email newsletter on Access. Take a look at the past issues -  (recommended site GR)

  http://www.saicomsystems.com/index.asp?id=pasttips

Contact Nirmala: (65) 897 1030 nirmala@saicomsystems.com

Related Documents at VB123

Microsoft Access Time Control Form
Consolidation Queries
Multiple Selection List Boxes
Transaction Queries

Click this button for the next page in the loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals