Queries On Steroids … Part I

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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles