dcsimg

Queries On Steroids ... Part I

November 14, 2006

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

Please list ALL phone numbers related to this contact.
Show all items related to the current topic.
Include all notes in a single field of the query.

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).

qry_Author_Titles

Author

Title

 Albert Ringer

 Is Anger the Enemy?

 Albert Ringer

 Life Without Fear

 Dean Straight

 Straight Talk About Computers

 Marjorie Green   

 But Is It User Friendly?

 Marjorie Green

 Cooking with Computers

 Marjorie Green

 Net Etiquette

 Marjorie Green

 Sushi, Anyone?

 Marjorie Green

 The Busy Executive's Database Guide   

 Marjorie Green

 You Can Combat Computer Stress!

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.

qry_Author_Titles_Smart

Author

Titles

 Albert Ringer

 Is Anger the Enemy?
 Life Without Fear

 Dean Straight

 Straight Talk About Computers

 Marjorie Green   

 But Is It User Friendly?
 Cooking with Computers
 Net Etiquette Sushi, Anyone?
 The Busy Executive's Database Guide   
 You Can Combat Computer Stress!

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:

1.  Create a function that will be called from the query.

2.  Function accepts an argument corresponding to the data row's PRIMARY KEY.

3.  A user-supplied delimiter character is used to separate data values.

4.  A DAO recordset is created and looped to generate output string.

5.  Function returns this output as a string value.

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers