Click here to Skip to main content
Click here to Skip to main content
Go to top

Add some AI to your combo box lookups

, 15 Dec 2010
Rate this:
Please Sign up or sign in to vote.
Helping the combo box autocomplete function with a frequency count.

receipts.jpg

Introduction

If you do a lot of manual input on database applications, you probably appreciate the autocomplete function of combo boxes. As opposed to the standard first-letter response of the default Windows combo box control, there is a myriad of combo box implementations out there. You can find some very impressive yet free implementations, ranging from plain JavaScript to AJAX controls; checkboxes or radio buttons per item, icons or images, multiple columns, multiple first-letters matching, color (or format) grouping, and any position letter matching (with highlighting).

My needs are rather simple; I want the Access style combo box, that allows for first-letters matching and multiple columns, and I want the first match to be the most frequently used one or the most likely match. This can be accomplished strictly through SQL.

To illustrate, let's say you need to make a choice of employee between John Smith from HR, Jane Smith from HR, and John Smith from Sales.

The first approach would be to query the employee table and pack (concatenate) the three columns (lastname, firstname, department) in a single (alphabetically ordered) column, yielding:

Smith Jane HR
Smith John HR
Smith John Sales

"Jan" will show up in the combo box after typing "Smith J". What I want is for "John" to show up first, simply because there are more Johns than Janes.

The second approach is to keep fields separate in distinct columns. Now, the order clause uses the three columns:

Smith | Jane | HR
Smith | John | HR
Smith | John | Sales

This does not accomplish much, but by adding a frequency count of lastname and firstname (namely how many John Smiths, Jane Smiths, etc., are there in the employee table) things start to look promising:

Smith | 1 | Jane | HR
Smith | 2 | John | HR
Smith | 2 | John | Sales

By issuing a new order clause with the frequency column in descending order, we can produce:

Smith | 2 | John | HR
Smith | 2 | John | Sales
Smith | 1 | Jane | HR

Now the first John will appear after typing "Smith J". This saves me from at least one tap of the down arrow key.

Implementation

As mentioned earlier, this was only an illustration. My real world application involves two tables with a one-to-many relationship. I have a table for stores (retailers, suppliers, repair shops, etc.) and a related table with receipts from such stores. Since many stores are actually retail or service chains, I keep the store name and number in separate fields. Originally, this helped me summarize (group) my "7-Eleven" expenses independently of which 7-Eleven I visited.

What I need is for the historically most frequently visited stores to appear in descending order. Here is a partial display of the original store combo box used to enter new receipts in a receipt form:

Store    Description    Address
346    7-Eleven 24625    3230 W DAVIS ST
341    7-Eleven 26264    100 N GREENVILLE AVE
132    7-Eleven 32870    1004 W MCDERMOTT R
171    7-Eleven 33302    10565 CUSTER RD
15     7-Eleven 33690    7818 ROWLETT RD
12     7-Eleven 33697    3975 LAKE FOREST

The Store column is a unique ID, the Description column concatenates store name and number, and the third column is just a memory aid. Here's the T-SQL statement that is used as the record source for the combo box:

SELECT   Store,
         Name & ' ' & Number AS Description,
         Address

FROM     Stores

ORDER BY Name, Number;

To use the visit frequency in the order clause, we first need to keep the name and number separate, just as in the illustration:

SELECT   Store,
         Name,
         Number,
         Address

FROM     Stores

ORDER BY Name, Number;

Now we can use this query (let's call it q1) as the source for a group query that counts receipts per store:

SELECT   Store,
         Name & ' ' & Number AS Description,
         Address

FROM     q1 LEFT JOIN Receipts ON q1.Store = Receipts.Store

GROUP BY Store, Name, Number, Address

ORDER BY Name, COUNT(Receipts.Receipt) DESC, Number;

The query yields the following result:

Store  Description     Address               CountOfReceipt
15     7-Eleven 33690  7818 ROWLETT RD       30
346    7-Eleven 24625  3230 W DAVIS ST       1
341    7-Eleven 26264  100 N GREENVILLE AVE  1
132    7-Eleven 32870  1004 W MCDERMOTT R    1
171    7-Eleven 33302  10565 CUSTER RD       1
12     7-Eleven 33697  3975 LAKE FOREST      1

Technically, there is no grouping because all the fields that make a store unique participate in the group clause. Also notice, we still use the original concatenation of store name and number; the number just needs to participate in the order clause.

The receipt count is shown for illustration purposes only. Although the list will appear counterintuitive or in disarray at first, chances are that the 7-Eleven visited 30 times is the next one I will visit. This will save me from displaying the list, and the number of keys or the time scrolling down the list looking for the right choice. The savings are progressive, meaning that when another 7-Eleven gains a visit, it will appear in second place in the list. It is also important to keep ordering by store number, so that it takes precedence when the receipt count is the same for two or more stores, as shown in the previous sample.

Incidentally, not all stores have a store number, and some "numbers" are actually alphanumeric, as in "3A" or "II". Let's see how to handle those.

A store can be unique in database terms (e.g., there is a single record for "Lake View Florist"). Some chain stores keep their store numbering internally; there's no hint to it on the receipts or their websites. In such cases, the store number is left blank (with a value of NULL) and some "immediate if" logic is added around the number in the description field so that the concatenation will not fail (and so that it can be trimmed). If you need the nitty-gritty, here's the T-SQL (Access/VB) version of the description column:

Description: q1.Name & IIf(IsNull(q1.Number),'',' ' & q1.Number)

With respect to alphanumeric store numbers, there is not much we can do. If you make the store number field a strictly numeric field, you will have to devise your own mapping for those stores using alphanumeric store numbers (codes). If you keep it as a text field, you will have to live with some aberrations, such as having Cici's Pizza 7006 shown before Cici's Pizza 94 in the list, just because they have the same number of receipts and they were entered in that order (the store ID is the hint):

Store    Description         Address                   CountOfReceipt
84       Cici's Pizza 181    3520 LAKEVIEW PKWY        6
85       Cici's Pizza 1      2936 LAVON DR             3
87       Cici's Pizza 10     2220 COIT RD STE 300      2
86       Cici's Pizza 7006   1105 E PARKER RD STE 112  1
340      Cici's Pizza 2      N GREENVILLE AVE          1
360      Cici's Pizza 94     479 E I30                 1

Either you renumber stores with preceding zeros, or you use another expression to perform the ascending numeric order in q1:

Number1: Val(Nz([Number],0))

The Nz function replaces nulls with 0, and the Val function takes care of "3A" and "II". Unfortunately, this is not a perfect solution; "3A" will be turned into 3 and "II" into 0. Furthermore, if stores are coded with a preceding alphabetic character (e.g., "A1", "B3", etc.), such codes will also be zeroed out by this approach. I have tried some additional logic without success. Let me know if you have a way around it; the goal is to preserve strings starting with a letter, and convert strings starting with a number to actual numeric values.

So far the preceding expression accomplishes a frequency sort, with the proper store number sort as fallback:

Store    Description        Address                     CountOfReceipt
84       Cici's Pizza 181   3520 LAKEVIEW PKWY          6
85       Cici's Pizza 1     2936 LAVON DR               3
87       Cici's Pizza 10    2220 COIT RD STE 300        2
340      Cici's Pizza 2     N GREENVILLE AVE            1
360      Cici's Pizza 94    479 E I30                   1
86       Cici's Pizza 7006  1105 E PARKER RD STE 112    1

Here's the final q1 T-SQL statement:

SELECT   Store,
         Name,
         Val(Nz([Number],0)) AS Number1,
         Address

FROM     Stores

ORDER BY Name, Val(Nz([Number],0));

And the record source for the combo box:

SELECT   Store,
         Name & IIf(Number1=0,'',' ' & Number1) AS Description,
         Address

FROM     q1 LEFT JOIN Receipts ON q1.Store = Receipts.Store

GROUP BY Store, Name, Number1, Address

ORDER BY Name, COUNT(Receipts.Receipt) DESC, Number1;

So there you have it. A frequency count (or likelihood) ordering method for your one-to-many relationships (e.g., order and order details, products and expenses, etc.). A little artificial intelligence never hurt anyone; most users may not appreciate it at first, but after 2 or 3 hundred lookups a day, they just might!

Points of Interest

I tried to make the T-SQL statements as generic as possible; if you actually use the code in MS Access, make sure to use double quotes instead of single quotes in the VB functions.

History

First version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

hector [.j.] rivas
Software Developer (Senior) CompuCom, Inc.
United States United States
Hector J. Rivas has 25+ years of experience managing hardware and software development under many different operating systems, platforms and languages. He has developed microcontroller interfaces and PC games; authored computer based training lessons and delivered fully functional financial and administrative data-intensive applications, as well as image processing and other calculation-intensive applications. Mr. Rivas has also managed Y2K remediation, large scale platform migration and Web site projects, from R&D to actual deployment.
Follow on   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmvpAbhinav S7-Jan-11 17:48 
Generaluseful PinmemberCIDev7-Jan-11 9:12 
GeneralMy vote of 5 PinmemberKing_kLAx22-Dec-10 4:50 
GeneralRe: My vote of 5 Pinmemberhector.j.rivas24-Dec-10 6:31 
GeneralMy vote of 5 Pinmemberprasad0222-Dec-10 4:04 
GeneralAdd some AI to your combo box lookups Pinmembertaniasiemence22-Dec-10 0:55 
GeneralMy vote of 5 Pinmembertaniasiemence22-Dec-10 0:50 
Good article. Code explanation is very nice.
 
http://godwinsblog.cdtech.in/2010/12/requested-page-cannot-be-accessed.html
GeneralMy vote of 4 Pinmemberguayasen_o20-Dec-10 3:44 
GeneralSimple yet practical solution: 5! PinmemberDrABELL16-Dec-10 18:34 
GeneralRe: Simple yet practical solution: 5! Pinmemberhector.j.rivas17-Dec-10 4:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 15 Dec 2010
Article Copyright 2010 by hector [.j.] rivas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid