[Webinar] Streamline your web hosting managementRegister Today


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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

591 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