Flatten relational data for summary/export

Question

Say we have two tables in an MS Access db:

Service Users:

| ID | Name | Other details... |
| 1  | Joe  | Blah...          |
| 2  | Fred | Qwerty...        |
| 3  | Bob  | Something else...|

Teams providing services:

| ID | TeamID | UserID |
| 1  | T1     | 1      |
| 2  | T2     | 1      |
| 3  | T2     | 2      |
| 4  | T3     | 2      |
| 5  | T3     | 3      |

I need to produce a summary query that produces a single row for each user, with the first several teams (by TeamID) assigned sitting in separate columns. Like:

Query:

| UserID | Name | Team1 | Team2 |
| 1      | Joe  | T1    | T2    |
| 2      | Fred | T2    | T3    |
| 3      | Bob  | T3    | Null  |

I can get the Team1 column using max() from a sub select query, but I'm having a complete mental block on how to achieve Team2, Team3, etc. (Yes, I know that if there are more teams assigned to a user than I create columns the query will lose that information: that isn't a concern).

Edit: To clarify, the number of columns in the query will be fixed (in the actual query, there will always be 7). If there are less teams than columns the additional columns should be Null (as in example). If there are more teams than columns, only the first 7 teams will be shown in this summary.

Edit 2 - Possible solution which doesn't work...:

I tried...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1
FROM UserTable

... which works fine. Unfortunately...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1,
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
AND TeamID <> Team1 ORDER BY TeamID) As Team2
FROM UserTable

... throws up a parameter box for Team1. An ideas on how to skip the first/second/etc... values from an jet query?

Answer 1

I'm not an MS-Access expert, but isn't there some way to skip rows in your sub queries? So for Team1 you skip 0 rows, for Team2 you skip 1 row, etc. Each time you use MAX() to select the maximum value from the rows in your sub query. Since there is one less row each time, you get another maximum value each time (if you sort your sub query result of course).

The general idea is that each of your sub queries (for Team1, Team2, ...) returns one less value such that your MAX() function returns another value each time.

Taking a look at what is possible with MS-Access SQL, you can only use TOP. But then you have to know how many rows your sub query has. That's impossible I think. Suppose you do know this and a user is a member of 3 teams, your sub query per team includes SELECT TOP(3) ..., SELECT TOP(2) ..., SELECT TOP(1)....

Answer 2

A crosstab query should suit.

Query 1: Called TeamUser

SELECT Teams.UserID, ServiceUsers.SName, Teams.TeamID
FROM ServiceUsers 
INNER JOIN Teams ON ServiceUsers.ID = Teams.UserID;

Crosstab

TRANSFORM First(TeamUser.TeamID) AS FirstOfTeamID
SELECT TeamUser.UserID, TeamUser.SName
FROM TeamUser
GROUP BY TeamUser.UserID, TeamUser.SName
PIVOT TeamUser.TeamID;

EDIT in response to comments

It should be possible to combine the two queries and to use a union query to reduce the number of entries.

TRANSFORM First(t.ATeamID) AS FirstOfATeamID
SELECT t.UserID, s.SName
FROM (SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "1st" As TeamCount
      FROM Teams
      GROUP BY Teams.UserID
      UNION 
      SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "2nd" As TeamCount
      FROM Teams
      WHERE ID Not In (SELECT First(Teams.ID) 
                       FROM Teams GROUP BY Teams.UserID)
      GROUP BY Teams.UserID) t
INNER JOIN ServiceUsers s ON t.UserID = s.ID
GROUP BY t.UserID, s.SName
PIVOT t.TeamCount

Answer 3

First, you need a query to assign a number from 1 to N to the teams associated to a user:

SELECT UserID, TeamID, 
       (SELECT count(*) FROM TeamTable t2 WHERE t2.TeamID <= TeamTable.TeamID and t2.UserID=TeamTable.UserID) AS position 
FROM TeamTable 
ORDER BY TeamID

Let's call this query TeamList. Now, you can use this query in the main query, by calling it 7 times, each time filtering a different position:

SELECT UserTable.ID As UID, UserTable.Name, 
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=1) As Team1,
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=2) As Team2,
   [...]
FROM UserTable

You could assemble all this in a single query, but it's more practical to define the TeamList query and calling it multiple times. Also note that this way the numbering is based on the order of TeamID. You can choose another order by changing the TeamList query, but the field you choose must have different unique values for each Team (or the <= comparison will generate wrong numbers).

Obviously, with a big number of rows performance would be terrible, but for a few hundreds it could be acceptable. You have to try.