February 19, 2009

More natural SQL display and efficient sorting in Access list controls

  • Date: October 27th, 2008
  • Author: Susan Harkins

Concatenating data can increase readability and friendliness without losing a bit of functionality.

cialis tabs


It’s common to populate a list control with a SQL statement, especially if you want to concatenate fields. For instance, the following SQL statement displays a list of names in multiple columns:

SELECT EmployeeID, LastName, FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig1r.jpg

The control’s Column Count property is 3 and the Column Width property is 0. That way, the control can pass a record’s primary key value (EmployeeID) unseen.

The ORDER BY clause sorts the rows by LastName. The solution is typical, but Access can do better. We’re just not used to viewing names in columnar format. Concatenating the names into a single column, as follows, displays a more familiar format:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig2r.jpg

I want to caution you against sorting on the concatenated field as follows:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName & ", " & FirstName

It’ll work, but if the data source contains a lot of data, it won’t perform well. Sorting by individual columns is more efficient, especially if the fields are indexed; Jet can’t use an index against the concatenated results of the ORDER BY clause. Besides, it’s awkward and unnecessary. Just specify sort fields in the ORDER BY clause as you normally would — displaying concatenated data doesn’t change the way you sort.

There’s still a way to improve the display. Perhaps it really isn’t an improvement, but a different way to present the data. Transpose the names, displaying the first name first, in a more natural format. You can still sort by last names, as follows:

SELECT EmployeeID, FirstName & " " & LastName

FROM Employees

ORDER BY LastName

october2008officeblog10fig3r.jpg

Don’t worry if a sort field isn’t in the SELECT clause’s field list — SQL doesn’t require that. SQL requires only that the sort field be in the underlying data source.

Permalink • Print • Comment

Leave a comment

You must be logged in to post a comment.

Made with WordPress and Semiologic • Sky Gold skin by Denis de Bernardy