MS Access Query question

Posted on 2011-09-28
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 92

Accepted Solution

Patrick Matthews earned 500 total points
Comment Utility
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

Comment Utility

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

Comment Utility
@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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now