How to Display Multiple Results in a Single Query Field in Your Access

If you’ve ever needed to produce a delimited list of field/query values alongside other information from your database you’ll know it can be challenging at best – and a task most people think isn’t going to be possible. If, for example, you’ve been asked to produce a report of how who were the top 5 sales people – in Descending order – for each month of the year, or the closest 3 stores to each customer, then you’re potentially left with wrangling with crosstab queries or manually combining the results outside of Access. But with the help of a little VBA code, you’ll be producing results like the following in minutes…

There are many times when it would be useful to be able to display a delimited list of results as part of a larger query – a list of the top 3 largest customers for each sales rep, the closest 5 stores to each of your highest spending customers, the list goes on.

Stop stitching your report together

While it’s more than possible to get the detail to create that kind of report, you’d usually end up having to manually stitch the results together outside of Access as part of a spreadsheet or report.

The following code cuts out the need to fiddle with the query results outside of Access, and instead, will allow you to produce the kind of results shown above with ease.

First, let’s outline what we want the code to be able to do:

“Given a query, return x number of results, combined into a single string that is delimited by y”

Before we begin

Before we look at the code, it’s important to note that while it’s possible to create a VBA function that is more generic, and capable of handling any query combination, doing so would result in pretty lengthy code so what we’ll do in this article is to define an example case and write the function to handle that case. Doing it this way will make it much easier for you to be able to recreate the code to meet your specific needs.

The sample case

We have a sales database that stores details of customer purchases, along with the category of product that the purchase belongs to. We want to produce a report that shows, by month, the top 3 categories in descending order.

For simplicity we’ll keep the example to a single table, although the principal will work regardless of your setup. The table we will be using is set up as follows:

Obviously this is an overly-simplified setup, but you get the idea.

Now – the code

Public Function CategoryList(Month As String, NumResults As Integer, SortAscending As Boolean, Delimiter As String) As String
    Dim sSql, resultString As String
    Dim rst As Recordset
    Dim firstLine As Boolean
    
    'Create our SQL string using the supplied parameters
    sSql = "SELECT TOP " & NumResults & " [category] FROM sales GROUP BY Format([TransactionDate],""mmm""), sales.Category HAVING (((Format([TransactionDate], ""mmm"")) = """ & Month & """))"
    If SortAscending Then sSql = sSql & " ORDER BY Sum(sales.TransactionValue) DESC;"
    
    Set rst = CurrentDb.OpenRecordset(sSql)
    
    firstLine = True
    
    'Loop through the results, and create the string to return
    With rst
        Do While Not .EOF
            If Not firstLine Then
                resultString = resultString & Delimiter & .Fields("category")
            Else
                resultString = resultString & .Fields("category")
                firstLine = False
            End If
            
        .MoveNext
        Loop
    End With
    
    Set rs = Nothing
    CategoryList = resultString
End Function

The code – explained

As you can see, the code is pretty self-explanatory. We start by using the parameters supplied to build our query. Once the query string is created, we execute it and loop through the results, concatenating them together to form the single result string we’re looking for.

You’ll notice that we check whether or not we should be adding a delimiter to the string or not – without this check you’d end up with either a poorly formatted result string, or you would have to add code to remove unwanted delimiters – this way keeps things nice and tidy from the outset.

The function itself can of course be called from within a query, a report, or anywhere you like from within Access.

Fix Corrupted Access Database

If you encounter corrupted Access databases when you run the above code, then it is better to use some specialized tool to repair them and recover all the tables from the corrupt databases.

Author Introduction:

Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL problem and excel recovery software products. For more information visit www.datanumen.com