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.
