Queries On Steroids … Part I

There are some things you just can’t do with SQL. The requests usually go something like this …

This is difficult to do in a query … and impossible to do with ANSI SQL alone!  Read on as I explain how this task might be accomplished with the use of a semi-generic function you create in MS Access.

> As usual, the code for this article is available for download.

The Problem Defined

The basic problem is this: For each author, there are multiple titles. So if I do a SQL join from the [authors] table to the [titles] table (through the many-to-many [titleauthor] table), it produces multiple rows for each author where the author is associated with more than one title. (Notice query output below).

Sometimes, this is exactly the output we want, but for this spec, what we want is one row per author, with ALL of their titles in a single cell, like the output below. This cannot be accomplished by means of a SQL statement, no matter how complex the joins.

The only way to accomplish this is by calling a VBA function from the query, as shown in the screen shot (below) of the Access Query Design Grid. This query calls the GetTitles() function twice (Titles and Titles2 columns) to display the results in two different ways. We’ll explain that in minute when we describe the code behind this function, but it underscores the reusability of such functions.

What’s Your Pleasure?

I can’t count the number of responses I had to the article I wrote back in August 2003 on this subject. It was titled Concatenate Column Values and used VBA code to pivot a query into a temp table for reporting on multi-valued rows. It seems that this idea of combining multiple columns into a single row is very popular, and while that article was fun, it really wasn’t that practical. In addition, adapting it to work for slightly different scenarios was really complicated and I spent many hours assisting readers with their code. This time, we try a different approach.

The approach described below is easier to understand and more extensible. You may need (or want) more than one of these functions, but they will all look very similar to what is shown below. The process goes as follows:

For our example, we’re going to make the function specific to the problem of returning the book titles for a given author. In this case, AuthorID is actually a string value that possibly corresponds to the author’s social security number. Since SSN is a text value, our first argument is a string, sID. The second argument of the function, sDelim, is the character you wish to insert in-between titles. It could be a comma, a space, the pipe character or a line feed.

(For the record, from VBA we can use vbCrLf to represent a Carriage Return/Line Feed combo, but from a query we must use the actual characters, which translate to Chr(13) & Chr(10), as shown above in the screen shot.)

For your applications, you will need to change the SQL Statement used to return the recordset. If your key value is numeric, then omit the single quotes. If your key is text that might include single quotes, you either need to double them with the replace function, or enclose the key with double quotes, not single quotes, something like one of these two options …

sSQL = “SELECT title FROM qryTitles ” & _

           “WHERE AuthorName =

              ” & “””” & sID & “””” & _

           ” ORDER BY title;”

or …

sSQL = “SELECT title FROM qryTitles ” & _

           “WHERE AuthorName =

              ‘” & Replace(sID, “‘”, “””) & “‘ ” & _

           “ORDER BY title;”

Hopefully, you will be looking up records by their numeric IDs so this will be a moot point, but there are probably applications like this Pubs database where entities are identified by text based keys.

The rest of the code is pretty basic. An output variable, sOut, is repeatedly concatenated with the current item value and delimiter string. When finished, the last delimiter that was tacked on is stripped off. Where the string is empty, that call is skipped to avoid an error and the result is returned by the function.

Public Function GetTitles(sID As String, sDelim as String) As String

    On Error Resume Next

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim sSQL As String

    Dim sOut As String

    Set dbs = CurrentDb

    sSQL = “SELECT title FROM titles INNER JOIN titleauthor ” & _

           “ON titles.title_id = titleauthor.title_id ” & _

           “WHERE au_id = ‘” & sID & “‘ ” & _

           “ORDER BY title;”

    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
    Do Until rst.EOF

        sOut = sOut & rst!title & sDelim

        rst.MoveNext

    Loop
    If Len(sOut) > Len(sDelim) Then

        sOut = Left(sOut, Len(sOut) – Len(sDelim))

    End If
    Set rst = Nothing

    Set dbs = Nothing
    GetTitles =

      sOut
End Function

From here …

As previously mentioned, you are going to have to modify this function to meet your specific needs. It’s not that I don’t like email, but before you write for help, step through the code in debug mode and watch what is happening. The most important pieces of this process are the KEY that is passed to the function, and the SQL used to return a filtered recordset based on that key. If your results look wrong, these are the elements of the function to suspect.

If you’re clever, you can probably think of ways to make this function more generic. It could accept the name of a sorted query as an additional argument and if the data type of the key was certain, the SQL statement could be generated on the fly, making this function very, very useful indeed.

Well, that concludes Part I of Queries on Steroids. If you enjoyed this discussion, check back next month for Part II. If you have questions, or would like to suggest a query topic, email me at Danny Lesandrini with your comments.

» See All Articles by Columnist Danny J. Lesandrini