• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

MS Access Query question

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.
1 Solution
Patrick MatthewsCommented:
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:


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!

shuittnyAuthor Commented:
@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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now