transposing rows to columns

[James Jones]

I have two tables as follows (only showing minimum fields)

Rooms
RoomCode

RoomCode is the primary key

Guests
GuestID
RoomCode
FirstName
SecondName


GuestID is the PK and RoomCode is the FK and there is a one to many
relationship Rooms to Guests respectively max 2.

I need to be able to list all the Guests in each Room something like this

RoomCode FirstName1 SecondName1 FirstName2 SecondName2

for inclusion in a report or form at some stage (not sure which yet.)

So basically trsanspose the list of guests intop columns. I have a sneaky
suspicion this isn't possible but does anyone know if it is in one or
multiple querries I can combine into one later?


Thanks

 

[Gerald Stanley]

Try using a UNION query that allows for either 1 or 2
guests to be staying, e.g. something along the lines of
SELECT G1.roomid, G1.firstName As firstName1, G1.lastName
As lastName1, G2.firstName As firstName2, G2.lastName As
lastName2
FROM Guests AS G1 INNER JOIN Guests AS G2 ON G1.roomid =
G2.roomid AND G1.guestId < G2.guestId
UNION
SELECT G.roomId, G.firstName, G.lastName, "",""
FROM Guests As G
WHERE (SELECT COUNT(*) FROM Guests WHERE roomId = G.roomId)
= 1;

Hope This Helps
Gerald Stanley MCSD

 

[Duane Hookom]

You could create a report of the rooms with a multiple column subreport of
the guests. If you want to do this in a query, consider the method and
function demonstrated in the sample concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[James Jones]

Try using a UNION query that allows for either 1 or 2
guests to be staying, e.g. something along the lines of
SELECT G1.roomid, G1.firstName As firstName1, G1.lastName
As lastName1, G2.firstName As firstName2, G2.lastName As
lastName2
FROM Guests AS G1 INNER JOIN Guests AS G2 ON G1.roomid =
G2.roomid AND G1.guestId < G2.guestId
UNION
SELECT G.roomId, G.firstName, G.lastName, "",""
FROM Guests As G
WHERE (SELECT COUNT(*) FROM Guests WHERE roomId = G.roomId)
= 1;

Hope This Helps
Gerald Stanley MCSD

WOW. Thanks for that didn't think this was possible

Works a treat. Looks like I need to go learn something more about Unions
as it's not something I have ever thought useful before.

If you will excuse me now I am off to see how far I can get before I break
it irreprably

 

[James Jones]

You could create a report of the rooms with a multiple column
subreport of the guests. If you want to do this in a query, consider
the method and function demonstrated in the sample concatenate
function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Thanks for that Duane. That link is great. I think it will take me longer
to understand than Geralds Union help though.

Still it's appreciated.