Go Premium for a chance to win a PS4. Enter to Win


MS Access Query question

Posted on 2011-09-28
Medium Priority
Last Modified: 2013-02-13
I wish I could award more than 500 points but oh well.  I also wish I could have come up with a better title.

In this example I have 3 main tables  (state, nfl_team, nhl_team) and 2 join tables (one that joins nfl and state and another that joins nhl and state) that I am entering via a form.  The values for the nfl and nhl teams are being entered via a subform within the form for the state table.  These tables are linked by the state_id field.

These are the values that I entered in the state form.....

State      NFL Team      NHL Team
NY      Giants      Rangers
      Jets      Islanders
CA      49ers      Sharks
      Raiders      Kings
      Chargers      Ducks
DC      Redskins      Capitals
PA      Eagles      Flyers
      Steelers      Penguins

I want to create a query that displays all nfl and nhl teams where state = “DC” or “PA” (I could have picked NY and/or CA but didn’t feel like typing all the possible combinations).  This is what I would expect to see.
PA      Eagles      Flyers
PA      Eagles      Penguins
PA      Steelers      Flyers
PA      Steelers      Penguins
DC      Redskins      Capitals

I was wondering if it was possible in within the query mode within MS Access 2003 to have that same recordset to display like the following below.
PA      Eagles      Steelers      Flyers      Penguins      
DC      Redskins            Capitals            

In which the number of columns is dynamic (since for any given state you could have x amount of teams per sport)

Is anything like this possible so that I can export the query to Excel?

Note - If I were using NY or CA there would be more columns due to the notion that those states have 3+ teams for each of the two sports (for the query that I want to have),  

Unfortunately I don't have a web browser and access on the same machine so I can show you the db diagram.  No matter, since my real case involves even more tables but I didn't want to scare you guys.
Question by:shuittny
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 36719469
You could try this:

1) Add this function to a regular VBA module:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    ' Requires reference to Microsoft DAO library
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    On Error GoTo ErrHandler
    ' Initialize to Null
    DConcat = Null
    ' Build up a query to grab the information needed for the concatenation
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            ' Initialize variable for this row
            ThisItem = ""
            ' Concatenate columns on this row
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            ' Trim leading delimiter
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            ' Concatenate row result to function return value
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
    End With
    ' Trim leading delimiter
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    GoTo Cleanup

    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    DConcat = CVErr(Err.Number)
    Set rs = Nothing
End Function

Open in new window

2) Run a query like this:

SELECT s.state, DConcat("[nfl_team_name]", "[nfl_team]", "[state] = '" & [state] & "'") AS NFL_Teams,
    DConcat("[nhl_team_name]", "[nhl_team]", "[state] = '" & [state] & "'") AS NHL_Teams
FROM state s
ORDER BY s.state

Open in new window

That function is described in my article:

LVL 10

Expert Comment

ID: 36719788

This may be possible using crosstab query/queries but I have a question first, why do you have separate join tables? If you have 2 tables, each with 2 columns, State and Team, then you can link these together using the state field. If you want more information on the State then you can have another table.

I'm sure you have a good reason for the structure you are talking about, but I can't help asking the question!


Author Comment

ID: 36815478
@plummet - in reality my issue was more complex than just a few tables and a few fields (altogether its 8 tables and many fields) but I opted to use a smaller subset for my example.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question