Attn Bob Quintal

[SharkFOA]

Hi Bob,

Hoping that you can help. Sometime ago I posted for help(Copied below). You
suggested using a crosstab query. I've tried everything I know how but can't
get it to work. I think it must be in the basics. You refer to a booked date
whereas I have a check in & check out date.

My table has a lookup to the room, checkin & checkout dates and I get a
figure(any kind is fine!) at the intersection of the two rows, (Room,
Checkin) & the column, Checkout date but not the intervening period. To
clarify, a room is booked from the 20/10/03 to 25/10/03. I only have the
date, 25/10/03 at the top whereas I need 20/10,21/10,22/10etc with a figure
underneath each.

This is what it should look like:
20/10 21/10 22/10 23/10 24/10 25/10
Room: x x x x x x

Original post:

Hi,

Hoping someone can help. I have built a booking database for a
facility with check in and check out dates, etc. What I need
to do is create a table with Rooms down one side and dates
across the top where the check in dates will put a cross in
the appropriate grid, i.e. Room 1 is booked from the 26/8/03 -
28/8/03. The grid will show a cross in row Room 1 under the
headings 26/8,27/8 & 28/8.This is so I can produce a report
showing which rooms are occupied and which aren't.
This data should not be a table, It should be a Crosstab Query.

Your crosstab query will take the data from a table with the two
fields Room_Number and BookedDate, and convert it to the format you
want.
I'm a newbie with SQL and understand that this is how I should
do it.

See the Help file that Came with Access, on the subject of Crosstab
Queries.

Bob

[Bob Quintal]

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Hoping that you can help. Sometime ago I posted for
help(Copied below). You suggested using a crosstab query. I've
tried everything I know how but can't get it to work. I think
it must be in the basics. You refer to a booked date whereas I
have a check in & check out date.

My table has a lookup to the room, checkin & checkout dates
and I get a figure(any kind is fine!) at the intersection of
the two rows, (Room, Checkin) & the column, Checkout date but
not the intervening period. To clarify, a room is booked from
the 20/10/03 to 25/10/03. I only have the date, 25/10/03 at
the top whereas I need 20/10,21/10,22/10etc with a figure
underneath each.

This is what it should look like:
20/10 21/10 22/10 23/10 24/10 25/10
Room: x x x x x x
OK, what you need is a calendar table (or query - note 1) to add to
your crosstab query.

This table needs to contain only one field, BookedDate. This can be
programmed to automatically give you the number of dates you need.
We can discuss that later if necessary.

Note 1: if you are sure that AT LEAST 1 room will have a checkin
date for every date you need, you could create a query that returns
the checkin date as BookedDate.

Your table contains (correct me if wrong), Room, Checkin, Checkout.
I have called it Bookings.

First you need a query to expand rooms x bookingDate

SELECT Calendar.BookedDate, Bookings.Room
FROM Calendar, Bookings
WHERE (((Calendar.BookedDate) Between [checkin] And [checkout]))
ORDER BY Calendar.BookedDate, Bookings.Room;

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

Bob Q
Original post:

Hi,

Hoping someone can help. I have built a booking database for
a facility with check in and check out dates, etc. What I
need to do is create a table with Rooms down one side and
dates across the top where the check in dates will put a
cross in the appropriate grid, i.e. Room 1 is booked from the
26/8/03 - 28/8/03. The grid will show a cross in row Room 1
under the headings 26/8,27/8 & 28/8.This is so I can produce
a report showing which rooms are occupied and which aren't.

This data should not be a table, It should be a Crosstab
Query.

Your crosstab query will take the data from a table with the
two fields Room_Number and BookedDate, and convert it to the
format you want.

I'm a newbie with SQL and understand that this is how I
should do it.

See the Help file that Came with Access, on the subject of
Crosstab Queries.

Bob

[SharkFOA]

Hi Bob,

Thanks for coming back to me.

Okay,

I've got the table called Bookings with romm, checkin & checkout..

I've made the query called calendar with the fields booked date,
room,checkin & check out and pasted in your SQL.

The third item is where I've run into trouble:

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");
I've tried different methods of creating the query but the SQL advises "Jet
database cannot find the query or table Qroom1 when I paste in the SQL

Is the crosstab query to be based on the calendar query?
Cheers,

--
SharkFOA

"Bob Quintal" <bq******@generation.net> wrote in message
news:57******************************@news.teranew s.com... "SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Hoping that you can help. Sometime ago I posted for
help(Copied below). You suggested using a crosstab query. I've
tried everything I know how but can't get it to work. I think
it must be in the basics. You refer to a booked date whereas I
have a check in & check out date.

My table has a lookup to the room, checkin & checkout dates
and I get a figure(any kind is fine!) at the intersection of
the two rows, (Room, Checkin) & the column, Checkout date but
not the intervening period. To clarify, a room is booked from
the 20/10/03 to 25/10/03. I only have the date, 25/10/03 at
the top whereas I need 20/10,21/10,22/10etc with a figure
underneath each.

This is what it should look like:
20/10 21/10 22/10 23/10 24/10 25/10
Room: x x x x x x

OK, what you need is a calendar table (or query - note 1) to add to
your crosstab query.

This table needs to contain only one field, BookedDate. This can be
programmed to automatically give you the number of dates you need.
We can discuss that later if necessary.

Note 1: if you are sure that AT LEAST 1 room will have a checkin
date for every date you need, you could create a query that returns
the checkin date as BookedDate.

Your table contains (correct me if wrong), Room, Checkin, Checkout.
I have called it Bookings.

First you need a query to expand rooms x bookingDate

SELECT Calendar.BookedDate, Bookings.Room
FROM Calendar, Bookings
WHERE (((Calendar.BookedDate) Between [checkin] And [checkout]))
ORDER BY Calendar.BookedDate, Bookings.Room;

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

Bob Q

Original post:

Hi,

Hoping someone can help. I have built a booking database for
a facility with check in and check out dates, etc. What I
need to do is create a table with Rooms down one side and
dates across the top where the check in dates will put a
cross in the appropriate grid, i.e. Room 1 is booked from the
26/8/03 - 28/8/03. The grid will show a cross in row Room 1
under the headings 26/8,27/8 & 28/8.This is so I can produce
a report showing which rooms are occupied and which aren't.

This data should not be a table, It should be a Crosstab
Query.

Your crosstab query will take the data from a table with the
two fields Room_Number and BookedDate, and convert it to the
format you want.

I'm a newbie with SQL and understand that this is how I
should do it.

See the Help file that Came with Access, on the subject of
Crosstab Queries.

Bob

[Bob Quintal]

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Thanks for coming back to me.

Okay,

I've got the table called Bookings with romm, checkin &
checkout..

I've made the query called calendar with the fields booked
date, room,checkin & check out and pasted in your SQL.

The third item is where I've run into trouble:

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");
I've tried different methods of creating the query but the SQL
advises "Jet database cannot find the query or table Qroom1
when I paste in the SQL

Is the crosstab query to be based on the calendar query?

If you reread my post. I explain that your calendar table is the
first step. All you need in calendar is a list of dates.
This list must contain all dates to appear in the crosstab.

So if you only have one room to book, and it is booked from October
22, 2003 to October 29, 2003, calendar must contain the
22nd,23rd,24th,25th,26th,27th,28th and 29th.

Then I gave you the SQL to join your calendar to your table. I
called it bookings because you never told me its name, only its
contents: room, checkin, checkout.

I called this query Qroom1. if you change its name, you need to
modify the second query I gave you (reposted above) to replace
QRoom1 with the name you chose (4 places)

Bob

Cheers,

--
SharkFOA

"Bob Quintal" <bq******@generation.net> wrote in message
news:57******************************@news.teranew s.com...

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

> Hi Bob,
>
> Hoping that you can help. Sometime ago I posted for
> help(Copied below). You suggested using a crosstab query.
> I've tried everything I know how but can't get it to work.
> I think it must be in the basics. You refer to a booked
> date whereas I have a check in & check out date.
>
> My table has a lookup to the room, checkin & checkout dates
> and I get a figure(any kind is fine!) at the intersection
> of the two rows, (Room, Checkin) & the column, Checkout
> date but not the intervening period. To clarify, a room is
> booked from the 20/10/03 to 25/10/03. I only have the date,
> 25/10/03 at the top whereas I need 20/10,21/10,22/10etc
> with a figure underneath each.
>
> This is what it should look like:
>
>
> 20/10 21/10 22/10 23/10 24/10 25/10
> Room: x x x x x
> x
>

OK, what you need is a calendar table (or query - note 1) to
add to your crosstab query.

This table needs to contain only one field, BookedDate. This
can be programmed to automatically give you the number of
dates you need. We can discuss that later if necessary.

Note 1: if you are sure that AT LEAST 1 room will have a
checkin date for every date you need, you could create a
query that returns the checkin date as BookedDate.

Your table contains (correct me if wrong), Room, Checkin,
Checkout. I have called it Bookings.

First you need a query to expand rooms x bookingDate

SELECT Calendar.BookedDate, Bookings.Room
FROM Calendar, Bookings
WHERE (((Calendar.BookedDate) Between [checkin] And
[checkout])) ORDER BY Calendar.BookedDate, Bookings.Room;

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

Bob Q

> Original post:
>
>
>> Hi,
>>
>> Hoping someone can help. I have built a booking database
>> for a facility with check in and check out dates, etc.
>> What I need to do is create a table with Rooms down one
>> side and dates across the top where the check in dates
>> will put a cross in the appropriate grid, i.e. Room 1 is
>> booked from the 26/8/03 - 28/8/03. The grid will show a
>> cross in row Room 1 under the headings 26/8,27/8 &
>> 28/8.This is so I can produce a report showing which rooms
>> are occupied and which aren't.
>
> This data should not be a table, It should be a Crosstab
> Query.
>
> Your crosstab query will take the data from a table with
> the two fields Room_Number and BookedDate, and convert it
> to the format you want.
>
>> I'm a newbie with SQL and understand that this is how I
>> should do it.
>
> See the Help file that Came with Access, on the subject of
> Crosstab Queries.
>
> Bob
>
>
>

[SharkFOA]

"Bob Quintal" <bq******@generation.net> wrote in message
news:b1******************************@news.teranew s.com...

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Thanks for coming back to me.

Okay,

I've got the table called Bookings with romm, checkin &
checkout..

I've made the query called calendar with the fields booked
date, room,checkin & check out and pasted in your SQL.

The third item is where I've run into trouble:

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

I've tried different methods of creating the query but the SQL
advises "Jet database cannot find the query or table Qroom1
when I paste in the SQL

Is the crosstab query to be based on the calendar query?

If you reread my post. I explain that your calendar table is the
first step. All you need in calendar is a list of dates.
This list must contain all dates to appear in the crosstab.

So if you only have one room to book, and it is booked from October
22, 2003 to October 29, 2003, calendar must contain the
22nd,23rd,24th,25th,26th,27th,28th and 29th.

Then I gave you the SQL to join your calendar to your table. I
called it bookings because you never told me its name, only its
contents: room, checkin, checkout.

I called this query Qroom1. if you change its name, you need to
modify the second query I gave you (reposted above) to replace
QRoom1 with the name you chose (4 places)

Thanks Bob,

I misread the calendar table part and thought when it referred to booked
date, it just needed that field, not the dates. Apologies and thanks.
Cheers
--
SharkFOA

[SharkFOA]

Hi Bob,

Brilliant. I only have two things left to do. This bit here:
1) "This table needs to contain only one field, BookedDate. This
can be programmed to automatically give you the number of
dates you need. We can discuss that later if necessary."

I manually put in some data and tested it and it works great but now need to
generate a table with all the dates, well at least the next 50 odd years!
And

2) I need to have it ask for start and end dates so that I can select the
range.

I greatly appreciate all your help.
Cheers,
--
SharkFOA

"Bob Quintal" <bq******@generation.net> wrote in message
news:b1******************************@news.teranew s.com...

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Thanks for coming back to me.

Okay,

I've got the table called Bookings with romm, checkin &
checkout..

I've made the query called calendar with the fields booked
date, room,checkin & check out and pasted in your SQL.

The third item is where I've run into trouble:

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

I've tried different methods of creating the query but the SQL
advises "Jet database cannot find the query or table Qroom1
when I paste in the SQL

Is the crosstab query to be based on the calendar query?

If you reread my post. I explain that your calendar table is the
first step. All you need in calendar is a list of dates.
This list must contain all dates to appear in the crosstab.

So if you only have one room to book, and it is booked from October
22, 2003 to October 29, 2003, calendar must contain the
22nd,23rd,24th,25th,26th,27th,28th and 29th.

Then I gave you the SQL to join your calendar to your table. I
called it bookings because you never told me its name, only its
contents: room, checkin, checkout.

I called this query Qroom1. if you change its name, you need to
modify the second query I gave you (reposted above) to replace
QRoom1 with the name you chose (4 places)

Bob

Cheers,

--
SharkFOA

> >>
Note 1: if you are sure that AT LEAST 1 room will have a
checkin date for every date you need, you could create a
query that returns the checkin date as BookedDate.

Your table contains (correct me if wrong), Room, Checkin,
Checkout. I have called it Bookings.

First you need a query to expand rooms x bookingDate

SELECT Calendar.BookedDate, Bookings.Room
FROM Calendar, Bookings
WHERE (((Calendar.BookedDate) Between [checkin] And
[checkout])) ORDER BY Calendar.BookedDate, Bookings.Room;

Your crosstab query should go something like this

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Room
FROM Calendar LEFT JOIN Qroom1 ON Calendar.BookedDate =
Qroom1.BookedDate
GROUP BY Qroom1.Room
ORDER BY Format([calendar].[BookedDate],"mm/dd")
PIVOT Format([calendar].[BookedDate],"mm/dd");

Bob Q

> Original post:
>
>
>> Hi,
>>
>> Hoping someone can help. I have built a booking database
>> for a facility with check in and check out dates, etc.
>> What I need to do is create a table with Rooms down one
>> side and dates across the top where the check in dates
>> will put a cross in the appropriate grid, i.e. Room 1 is
>> booked from the 26/8/03 - 28/8/03. The grid will show a
>> cross in row Room 1 under the headings 26/8,27/8 &
>> 28/8.This is so I can produce a report showing which rooms
>> are occupied and which aren't.
>
> This data should not be a table, It should be a Crosstab
> Query.
>
> Your crosstab query will take the data from a table with
> the two fields Room_Number and BookedDate, and convert it
> to the format you want.
>
>> I'm a newbie with SQL and understand that this is how I
>> should do it.
>
> See the Help file that Came with Access, on the subject of
> Crosstab Queries.
>
> Bob
>
>
>

[SharkFOA]

"Bob Quintal" <bq******@generation.net> wrote in message
news:b1******************************@news.teranew s.com...

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Sorry Bob, one more thing. The dates are the wrong way round for this side
of the world. October 20th is written 20/10.
Cheers,

--
SharkFOA

[Bob Quintal]

"SharkFOA" <sp**@spam.tw.uk> wrote in
news:3f******@clear.net.nz:

Hi Bob,

Brilliant. I only have two things left to do. This bit here:
1) "This table needs to contain only one field, BookedDate.
This can be programmed to automatically give you the number of
dates you need. We can discuss that later if necessary."

I manually put in some data and tested it and it works great
but now need to generate a table with all the dates, well at
least the next 50 odd years!
That would make a D@Mn! wide report. Better is to generate only the
few days you need at one time.

Create a code module and paste the following into it:
'----------------------
Public Sub setDateRange()
Dim strdate As String
Dim dDate As Date
Dim strDays As String
Dim iDays As Integer
Dim iDone As Integer

strdate = InputBox("Enter the starting date for the report")
If strdate = "" Then GoTo exit_setDateRange
dDate = CDate(strdate)
strDays = InputBox("Enter the number of days to show on the
report")
If strDays = "" Then GoTo exit_setDateRange
strDays = CInt(strDays)
DoCmd.RunSQL "DELETE * from calendar"
For iDone = 1 To strDays
DoCmd.RunSQL "Insert into calendar " & _
"(BookedDate) VALUES (#" & (dDate + iDone - 1) & "#)"
Next iDone
exit_setDateRange:
Exit Sub
End Sub
'----------------------

This populates the number of days you want to see at the top of
your report

Put a command button on the form that controls the report, and set
the On Click event of that button to event procedure, then click
the ellipsis (...). type setdaterange and save.

Run this as part of setting up the report.

And

2) I need to have it ask for start and end dates so that I can
select the range.

The code asks for start date and number of days. Less to type for
you.

As to your other question regarding the sequence of days/months vs
months/days, if you look at the query, you'll see it's easy to fix.

Hint: format(). Only change the one on the line starting with
PIVOT otherwise 31/january will come after 01/February.

Bob Q

[SharkFOA]

"SharkFOA" <sp**@spam.tw.uk> wrote in message news:3f******@clear.net.nz...

Hi Bob,

Thanks mate for all your help.
Cheers,

--
SharkFOA

[Bob Quintal]

"SharkFOA" <sp**@spam.tw> wrote in news:3f******@clear.net.nz:

"SharkFOA" <sp**@spam.tw.uk> wrote in message
news:3f******@clear.net.nz...

Hi Bob, Thanks mate for all your help.

You are very welcome.

Cheers,

--
SharkFOA