2

I'd really appreciate some pointers on how to handle a query that joins tables that does not return the Cartesian product but puts the below residents into columns on a single returned row. I need to generate some reports and I need to have all related residents from a space on one report per space. I do not have any sample code as I am unsure where to even start.

spaces Table

spaceID|rent
----------------
1      | 1000
2      | 1200

residents Table

id|spaceID|name
--------------------
1 |1      |Joe Smith
2 |1      |Jane Smith
3 |2      |Frank Zakk
4 |2      |Nancy Zakk
5 |2      |Sam Zakk

Expected Result

spaceID |rent  |resident1 |resident2 |resident3| resident4
1       |1000  |Joe Smith |Jane Smith|         |
2       |1200  |Frank Zakk|Nancy Zakk|Sam Zakk |

My Result using a join

spaceID|rent | name
--------------------------------
1      |1000 |Joe Smith
1      |1000 |Jane Smith
etc...
briarfox
  • 525
  • 5
  • 13
  • One thing that will enable a solution to this is having another column in the residents table that uniquely identifies the resident. Is it possible for you to add such a column? – Pat Jones Dec 14 '17 at 19:46
  • Yes I do have a unique primary key. – briarfox Dec 14 '17 at 19:48
  • create a pivot view. –  Dec 14 '17 at 20:04
  • Pivot views are not available in Access from 2013 onward, which I had no idea about (see [this](https://technet.microsoft.com/en-us/library/cc178954(v=office.15).aspx)). It's possible to do this with raw SQL but will take a bit of wrangling. What is the maximum number of residents that you'd have per space? It honestly might be easier for you to do this in Excel. – Pat Jones Dec 14 '17 at 20:14
  • Options: 1. generate a sequence ID for each space group using DCount() which would be used as the Column Header in CROSSTAB, https://stackoverflow.com/questions/45898593/transform-multiple-rows-into-columns-with-unique-key, however, CROSSTAB query is difficult to use as a source for a stable report http://allenbrowne.com/ser-67.html, 2. http://allenbrowne.com/func-concat.html – June7 Dec 14 '17 at 20:32
  • Max residents would be 4. – briarfox Dec 14 '17 at 21:00
  • June7 thanks for the links. I'd really like to use concat but it wont work as I need each resident as a separate field for the mail merge. If I could just create one query to pivot the names i could build off that query to solve the problem. I'm still stuck. – briarfox Dec 14 '17 at 21:53
  • Try `TRANSFORM` and `PIVOT` https://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access – Srini V Dec 14 '17 at 23:40
  • `TRANSFORM` and `PIVOT` don't quite accomplish what the OP is looking for, as it's not really a crosstab query in the normal sense, i.e. there is no aggregating taking place. – Pat Jones Dec 15 '17 at 18:35

2 Answers2

0

This does it for you, BUT it relies on the assumption that each resident within a space will have sequential ID numbers - and so in that way it is not a very general query. It is also somewhat of a mess syntactically, because Access SQL doesn't avail itself of some more powerful tools that say T-SQL does. I would really do this in Excel, which has built-in pivot capabilities.

SELECT tA.spaceID, tA.rent, tA.resident, tC.resident, tD.resident
FROM (((SELECT tblSpaces.spaceID, tblSpaces.rent, tblResidents.resident, tblResidents.id
        FROM tblResidents LEFT JOIN tblSpaces ON tblResidents.spaceID = tblSpaces.spaceID) AS tA
        INNER JOIN (SELECT spaceID, MIN(id) AS minID
                    FROM tblResidents
                    GROUP BY spaceID) AS tB ON tA.id = tB.minID)
        LEFT JOIN tblResidents AS tC ON tC.id = tA.id + 1 AND tC.spaceID = tA.spaceID)
        LEFT JOIN tblResidents AS tD ON tD.id = tA.id + 2 AND tD.spaceID = tA.spaceID

Note that I modified your table names to tblSpaces and tblResidents.

Pat Jones
  • 876
  • 8
  • 18
-2

Used Stuff Method to get the desired result.

IF OBJECT_ID('tempdb..#Spaces') IS NOT NULL    
DROP TABLE #Spaces
IF OBJECT_ID('tempdb..#Resident') IS NOT NULL 
    DROP TABLE #Resident 
create table #Spaces
(
    spaceID int, 
    rent decimal
)
create table #Resident
(
    ID int, 
    SpaceID int, 
    ResidentName varchar(50)
)
insert into #Spaces values(1,1000),(2,1200)
insert into #Resident values(1,1,'Joe Smith'),(2,1,'Jane Smith'),(3,2,'Frank Zakk'),(4,2,'Nancy Zakk'),(5,2,'Sam Zakk')
    Select spaceID, rent,ResidentName=STUFF((SELECT ' | ' + ResidentName   
             FROM #Resident b       
             WHERE b.SpaceID = a.SpaceID    
             FOR XML PATH('')), 1, 2, '')
                from(
                select A.spaceID,A.rent,B.ResidentName from   #Spaces A
                inner join #Resident B
                on A.spaceID=B.SpaceID)a
                group by spaceID,rent
Dharmendra Kumar Singh
  • 2,979
  • 10
  • 46
  • 63