Data Management

Jet SQL and T-SQL handle duplicate values differently

Access databases can use two formats, Jet SQL and Transact-SQL, and they don't handle queries using TOP and ORDER BY in the same way. Find out how the formats differ and how to work around it.

Access databases use Jet SQL—at least they use to. The more familiar Access database format (the .mdb file) has always used Jet SQL. Access projects, however, use Transact-SQL (T-SQL), which is the version of SQL that SQL Server uses. Access users unfamiliar with T-SQL and SQL Server might assume that any SQL that works in an .mdb file will run correctly in an Access project, but they'd be wrong.

One discrepancy occurs when you combine the TOP and ORDER BY clauses to return a specific number of items. For instance, you might want to return the 10 highest priced products you offer. If the 10th item's value is a duplicate, the query might return 10 or 11 items, depending on which Access format you're using. Jet and T-SQL TOP deal with duplicates differently, and in this article, we'll show you how to prepare for those duplicates in both Access formats.

The results of duplicate values
In both Access formats, you can limit a record using a combined form of TOP and ORDER BY as follows:
SELECT [ALL | DISTINCT] TOP value [PERCENT] * | fieldlist
FROM datasource

For instance, the following query returns the same five products in both Northwind.mdb and NorthwindCS.adp (the sample database and project that come with Access):
SELECT TOP 5 ProductName, UnitPrice
From Products

The problem sneaks in when there's a duplicate value in the last returned record. To illustrate this problem, run the following query in both Northwind sample files (.mdb and .adp):
SELECT TOP 9 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice

Figure A shows the resulting recordset in Northwind.mdb. As you can see, Jet SQL returns 10 records, not nine, as requested in the TOP clause. That's because the ninth (last) value is duplicated and Jet doesn't know which record to include. As far as Jet's TOP clause is concerned, both values are the same—$9.50—so Jet returns them both.

Figure A
Jet's TOP clause returns duplicate values.

By contrast, T-SQL returns just the required number of records, even if the last value is duplicated, as shown in Figure B (use NorthwindCS.adp).

Figure B
T-SQL's TOP returns only the specified number of records, regardless of duplicates.

Accommodating duplicate values
Fortunately, you can work around the duplicate problem in both formats—if you know which problem you're facing. Remember, an .mdb file will return all duplicate values that match the last selected value. A project will return only the number of records requested. Obviously, you need very different solutions.

A Jet workaround
When querying Jet, you need an extraneous field to weaken the standing of the duplicate values. The simplest solution is to add another field to the ORDER BY clause. That way, the record as a whole isn't duplicated and Jet can choose between the duplicate values in the value field. You're not really solving the problem in a logical manner; you're tricking Jet to return just the results you want.

Anytime you use the Jet TOP clause in a sorted recordset, you should consider whether you want to include duplicate values. Let's revisit the earlier query that returned 10 records instead of the requested nine in the .mdb database. Your first consideration might be to add the ProductName field to the ORDER BY clause as follows:
SELECT TOP 9 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice, ProductName

Doing so does force Jet to return just nine records instead of 10, as shown in Figure C. However, the value returned may or may not be adequate for your purposes. In this case, Jet returns Røgede sild, dropping Zaanse koeken from the list.

Figure C
Add another field to the ORDER BY clause.

In this particular case, which product Jet keeps and which product Jet drops don't seem to be terribly important. Let's suppose that you want the top nine products, and in the case of any duplicates, you want the product with the highest quantity currently in stock. In that case, you might consider adding the UnitsInStock field to the ORDER BY clause:
SELECT TOP 9 ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice, UnitsInStock DESC

This time, Jet returns Zaanse koeken instead of Røgede sild, as shown in Figure D. That's because Zaanse koeken's UnitsInStock value, 35, is greater than Røgede sild's corresponding value, 5. The sort retrieves Zaanse koeken before Røgede sild, and since the combined fields of UnitPrice and UnitsInStock produce no duplicate values, Jet returns only nine records. There's no right or wrong to your solution, but you must carefully consider the query's purpose to force Jet to evaluate duplicates to suit your purpose.

Figure D
Jet uses more specific criteria to choose the ninth record.

A T-SQL workaround
Working with T-SQL takes less guesswork than with Jet because T-SQL returns only the requested number of records—unless you force it to return duplicates using the WITH TIES clause. When you include this clause, T-SQL returns duplicate values. Returning to the earlier example in NorthwindCS.adp, add WITH TIES as follows:
SELECT TOP 9 WITH TIES ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice

Figure E shows the results: 10 records instead of nine.

Figure E
Add WITH TIES to include duplicates in T-SQL.

Know your SQL
Working successfully with an Access project requires knowledge of T-SQL because Access can use two versions of SQL. Sometimes Jet and T-SQL work the same way, but not always. Being prepared for the differences between the two versions is the key to success.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks