Working SQL: Ordered Calculations with SQL

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Jan-2002 >

Working SQL: Ordered Calculations with SQL

 

Mike Westcott

 

In this article, Mike Westcott shows how to use SQL to solve problems with counter columns, running sums, and all things ordered by using self-joins. Mike also demonstrates the power and danger of incorporating VBA into your queries.

 

BY using SQL instead of reaching for the nearest code module and looping through a recordset, you’ll be rewarded with all of the blazing performance that the Jet optimizer can offer. Recently, I was hired to speed up an Access application that used more than its fair share of DAO recordsets and temporary tables. By replacing its processing with SQL like the examples in this article, the application ended up running five times faster. Actually, I didn’t chop out all of the code; I left the progress meter routines alone.

 

Over the past few weeks I’ve seen several different situations that share an underlying principle: Each requires a calculation based on how data is ordered. The three real-life scenarios I’m going to consider are a counter column, a query to find the first missing number in a sequence, and a running sum. How real life? I’ve drawn these examples from questions posted to newsgroups that I review. Along the way, you should discover that you can do far more with the humble JOIN clause than you ever realized.

 

Borrowing the key

This first problem was to create a query that would display the data from a table (tblCustomer in Figure 1)  with an incrementing counter that would number each row in the table in ascending order: 1, 2, 3, and so on. The table uses an eight-digit alphanumeric CustomerCode as its primary key.

 

One of the first principles of relational data is that the rows have no particular order within a table. That’s because the relational model implements mathematical set theory, and the items within a set can’t be assumed to have any relationship other than membership of the same set. In this problem, however, a query needs to impose an order on the data, to find some way to compare it against itself. A new incrementing column needs to be introduced that makes use of a unique key in the data. In this table, CustomerCode will do nicely. The solution to the problem is to write a query that will look at each row in tblCustomer and ask how many other rows have a CustomerCode that comes before it in alphabetical order. The answer to this question will be the customer’s counter number.

 

An obvious way to compare columns, especially if performance is a consideration, is to use a JOIN. Since I need to compare a column against itself, I’ll join the table to itself, a technique known, understandably enough, as a self-join. If you’ve never had to do this before, you can start off in query design view by adding tblCustomer to the top panel of the query designer twice. To avoid confusion, Access will give the second copy the alias tblCustomer_1. The self-join is created by dragging the CustomerCode field in tblCustomer onto the CustomerCode field in tblCustomer_1. The next step is to add the CustomerCode fields from both tables to the grid in the lower pane (see Figure 2, on page 16, for the result).

 

This query won’t make me employee of the month. All I’ve done is return, for each CustomerCode, a matching CustomerCode. What I really want is to find out, for each CustomerCode, the set of CustomerCodes that come before it in the alphabet. To do this, I have to

switch into the SQL view, because I’m about to leave the Query Builder’s capability far behind. I’ll get the desired result by changing the JOIN operator from an equal to sign (=) to a greater than or equal to sign (>=) to give this:  ...

 

200201_mw1

Figure 1. Incrementing counter column.

 

Read More Here:

Working SQL: Ordered Calculations with SQL