Combining records in one table

[Guest]

I am creating a booking database for hotel bookings for meetings and I need
to create an output report. The records that are entered for the bookings
are of the format;

Person1, HotelName, Date1
Person1, HotelName, Date2
Person2, HotelName, Date1
Person2, HotelName, Date2 ...etc.

I want the output to look like;

Person1, HotelName, Date1, Date2
Person2, HotelName, Date1, Date2 ...etc.

The dates can be any value and will change from one meeting to another
(although these can be listed from a seperate query) and a member may stay
for any or all of the nights.

I have no idea how to start this one off, so any help would be gratefully
received

HB

 

[Guest]

Try a crosstab query --
TRANSFORM First(Table11.STAYDATE) AS [START OF STAY]
SELECT Table11.PERSON, Table11.HOTEL
FROM Table11
GROUP BY Table11.PERSON, Table11.HOTEL
PIVOT Table11.STAYDATE;

 

[Guest]

Thanks Karl, that did the trick!

It has given another problem though (don't they always??). I want to filter
the data by one of the fields, but when I apply the filter,

WHERE (((qry.MeetingID)=[Forms]![frm_Switch_Exports]![txt_ChooseMeeting]))

I get the error that the field is not recognised, but I know it is correct
as it is working on other queries. Can I apply a filter against a crosstab
query in this way???

thanks again for any help

Try a crosstab query --
TRANSFORM First(Table11.STAYDATE) AS [START OF STAY]
SELECT Table11.PERSON, Table11.HOTEL
FROM Table11
GROUP BY Table11.PERSON, Table11.HOTEL
PIVOT Table11.STAYDATE;

I am creating a booking database for hotel bookings for meetings and I need
to create an output report. The records that are entered for the bookings
are of the format;

Person1, HotelName, Date1
Person1, HotelName, Date2
Person2, HotelName, Date1
Person2, HotelName, Date2 ...etc.

I want the output to look like;

Person1, HotelName, Date1, Date2
Person2, HotelName, Date1, Date2 ...etc.

The dates can be any value and will change from one meeting to another
(although these can be listed from a seperate query) and a member may stay
for any or all of the nights.

I have no idea how to start this one off, so any help would be gratefully
received

HB

 

[John Spencer (MVP)]

With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![frm_Switch_Exports]![txt_ChooseMeeting]
Select the data type of the parameter in column 2

 

[Guest]

Thanks guys, these two answers have sorted me out