Microsoft Access new columns for data based on matching values

Question

I asked this question a few months back but now I'm working out of MS-Access and I'm unsure how to proceed. I have a query that lists the bldid, unitid, and resident id in separate columns. I'd like to modify the query output in Microsoft Access so that each resident that shares a building / unit shows as a new column on the same row as shown below. But since I'm limited to MS Access I can't seem to use with, cte, or rownumber. I'm at a loss as to how to do this so any help would be appreciated.

Query1

BldID	UnitID	ResidentID
1	201	John Smith
1	201	Jane Doe
1	202	Daniel Jones
1	202	Mark Garcia
2	201	Maria Lee
2	201	Paul Williams
2	201	Mike Jones

Desired Output from edited Query

BldID	UnitID	Res1	Res2	Res3
1	201	John Smith	Jane Doe
1	202	Daniel Jones	Mark Garcia
2	201	Maria Lee	Paul Williams	Mike Jones

Answer 1

You can use a Crosstab Query

TRANSFORM Max(Resident.ResidentID) AS MaxOfResidentID
SELECT Resident.BldID, Resident.UnitID
FROM Resident
GROUP BY Resident.BldID, Resident.UnitID
ORDER BY Resident.BldID, Resident.UnitID
PIVOT "Res" & (DCount("*",
               "Resident",
               "BldID=" & [BldID] & " AND UnitID=" & [UnitID] &
               " AND ResidentID<'" & [ResidentID] & "'") + 1);

If you need a constant number of columns (e.g. if you want to create an Access report), then you can add an In clause to this query (before the ;):

In ("Res1","Res2","Res3","Res4","Res5","Res6")

This always creates 6 columns with the same names.

The difficulty is to get the row number per BldID/UnitID group. This is achieved by

DCount(1,
       "Resident",
       "BldID=" & [BldID] & 
       " AND UnitID=" & [UnitID] &
       " AND ResidentID<'" & [ResidentID] & "'") + 1

where Resident is the name of your table or query. It counts residents having the same BldID and UnitID but having a smaller ResidentID. Adding 1 to this count yields the row number starting at 1. Then the string "Res" is prepended to build the column name.

Note that the residents are listed in alphabetical order form left to right.