Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 14, 2006

Queries On Steroids ... Part I

By Danny Lesandrini

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




 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.




 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
    If Len(sOut) > Len(sDelim) Then
        sOut = Left(sOut, Len(sOut) - Len(sDelim))
    End If
    Set rst = Nothing
    Set dbs = Nothing
    GetTitles =
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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM