Dynamic reports PTII

[SharkSYA]

After canvassing ideas it appears that the way I need to do it is not
possible.There are 126 rooms, more to be added, and it needs to print a
report or reports showing 76 days of bookings.

There are two queries involved:

Here's the code(Thanks Bob Q!)

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


And crosstab query:

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Date
FROM Calendar INNER JOIN Qroom1 ON Calendar.Date=Qroom1.Date
WHERE (((Qroom1.Date)=Date() Or (Qroom1.Date)>Date()))
GROUP BY Qroom1.Date
PIVOT Qroom1.room;


The ideal report has rooms across the top and the dates are down
the left hand side. If a room is booked an 'X' appears in the appropriate
box. My problem is that when rooms are added they appear in the query but
not in the report based in that query. I hope that this is clearer. It isn't
imperative that the dates be down the left, they could be across the top and
the rooms down the side.

Any suggestions on how to achieve this would be most welcome. I'm quite
happy to split the reports into seperate rooms or two lots of dates,i.e. 1-
38 days & 39 - 76 Days and will entertain any ideas to work around it.

I thought, possibly,it could be achieved by macros hence the new posting to
that newsgroup.

Cheers,

 

[MacDermott]

You might want to consider using Excel as your reporting tool.
With an Access report, you won't be able to get very many of your rooms (or
dates) across the top of each page.

HTH
- Turtle

 

[Roger]

instead of adding many (many) fields to the report, can you have one
big, long text field and then use the 'format' event to build a string
of X's

is the first query below qroom ?

 

[SharkSYA]

instead of adding many (many) fields to the report, can you have one
big, long text field and then use the 'format' event to build a string
of X's

How would I do that?

is the first query below qroom ?

Yes, it is.

Thanks for the reply, I think you might be onto something.


--
SharkSYA

After canvassing ideas it appears that the way I need to do it is not
possible.There are 126 rooms, more to be added, and it needs to print a
report or reports showing 76 days of bookings.

There are two queries involved:

Here's the code(Thanks Bob Q!)

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


And crosstab query:

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Date
FROM Calendar INNER JOIN Qroom1 ON Calendar.Date=Qroom1.Date
WHERE (((Qroom1.Date)=Date() Or (Qroom1.Date)>Date()))
GROUP BY Qroom1.Date
PIVOT Qroom1.room;


The ideal report has rooms across the top and the dates are down
the left hand side. If a room is booked an 'X' appears in the appropriate
box. My problem is that when rooms are added they appear in the query but
not in the report based in that query. I hope that this is clearer. It isn't
imperative that the dates be down the left, they could be across the top and
the rooms down the side.

Any suggestions on how to achieve this would be most welcome. I'm quite
happy to split the reports into seperate rooms or two lots of dates,i.e. 1-
38 days & 39 - 76 Days and will entertain any ideas to work around it.

I thought, possibly,it could be achieved by macros hence the new posting to
that newsgroup.

Cheers,

 

[Steve Jorgensen]

If I understand your question properly, I have done what you are talking
about. Basically, what you need is a pool of controls that are hidden by
default and may or may not be used. In the report's Open event handler, you
run a query that gathers up the data on what the column names will be, and
sets the label headers and textbox ControlSource properties accordingly. At
the same time, it makes all the used labels and columns .Visible=True.

Presto - a dynamic report. It's not trivial to code, but it's not horrible
either. The only thing to be careful to remember is that in the Open event
handler is the -only- place where you can set the ControlSource properties
from code at runtime when not in design mode, so that is where you must do it.

- Steve J.

"Trivial stuff is not always easy, but hard stuff isn't always much harder" -
Steve J. (to a client today)

 

[Salad]

After canvassing ideas it appears that the way I need to do it is not
possible.There are 126 rooms, more to be added, and it needs to print a
report or reports showing 76 days of bookings.

There are two queries involved:

Here's the code(Thanks Bob Q!)

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

And crosstab query:

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Date
FROM Calendar INNER JOIN Qroom1 ON Calendar.Date=Qroom1.Date
WHERE (((Qroom1.Date)=Date() Or (Qroom1.Date)>Date()))
GROUP BY Qroom1.Date
PIVOT Qroom1.room;

The ideal report has rooms across the top and the dates are down
the left hand side. If a room is booked an 'X' appears in the appropriate
box. My problem is that when rooms are added they appear in the query but
not in the report based in that query. I hope that this is clearer. It isn't
imperative that the dates be down the left, they could be across the top and
the rooms down the side.

Any suggestions on how to achieve this would be most welcome. I'm quite
happy to split the reports into seperate rooms or two lots of dates,i.e. 1-
38 days & 39 - 76 Days and will entertain any ideas to work around it.

I thought, possibly,it could be achieved by macros hence the new posting to
that newsgroup.

Cheers,

Here is what I did. You can check this out...take about 2 minutes. I created a
table with the following fields
RoomID Autonumber, Primary key
Room number
RoomDate Date/Time

I then added a few records for a couple off dates. I entered rooms 1,2,3. for
a few rows and then randomly entered a few dates for each room. Here is my
query.

TRANSFORM Count(Table4.RoomID) AS [The Value]
SELECT Table4.RoomDate, Count(Table4.RoomID) AS [Total Of RoomID]
FROM Table4
GROUP BY Table4.RoomDate
ORDER BY Table4.RoomDate
PIVOT Table4.Room;

When you run it, you get dates down the side, room numbers at the top, and if it
is to be occupied for that date then it has the number 1 in the column, else it
is null.

You can format it to check for the value of 1 or Null and put an X in the
report. No need for a second query.

There is a potential problem with your report if you have PREDEFINED columns at
the top for each room. Lets say you have rooms 1,2, and 3. Room 2 has no
occupancy within the date range. Your columns would be Date, Room1, Room3, no
number 2.

In this case, you might want to create another table, Call it RoomBlank. In
this table, you'd want to create the same structure, and create 76 rows (to
match the number of rooms you have). Enter all of the room numbers BUT leave ALL
of the dates blank.

Now create a query that collects to rooms within the date range and union it to
RoomBlank. You are now assured that all rooms will be listed for your report
whether or not there is an occupancy within the date range. It is with this
query you'd create the crosstab. When you run the report, you'd want to exclude
the row where the date is blank (null)

 

[SharkSYA]

If I understand your question properly, I have done what you are talking
about. Basically, what you need is a pool of controls that are hidden by
default and may or may not be used. In the report's Open event handler, you
run a query that gathers up the data on what the column names will be, and
sets the label headers and textbox ControlSource properties accordingly. At
the same time, it makes all the used labels and columns .Visible=True.

Good to hear it is possible!

Presto - a dynamic report. It's not trivial to code, but it's not horrible
either. The only thing to be careful to remember is that in the Open event
handler is the -only- place where you can set the ControlSource properties
from code at runtime when not in design mode, so that is where you must do

it.

Can this be done in SQL as I am unfamiliar with VBA?

Thanks for your help.

Cheers,



--
SharkSYA

- Steve J.

"Trivial stuff is not always easy, but hard stuff isn't always much harder" -
Steve J. (to a client today)

After canvassing ideas it appears that the way I need to do it is not
possible.There are 126 rooms, more to be added, and it needs to print a
report or reports showing 76 days of bookings.

There are two queries involved:

Here's the code(Thanks Bob Q!)

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


And crosstab query:

TRANSFORM IIf(IsNull([room]),"","X") AS Isbooked
SELECT Qroom1.Date
FROM Calendar INNER JOIN Qroom1 ON Calendar.Date=Qroom1.Date
WHERE (((Qroom1.Date)=Date() Or (Qroom1.Date)>Date()))
GROUP BY Qroom1.Date
PIVOT Qroom1.room;


The ideal report has rooms across the top and the dates are down
the left hand side. If a room is booked an 'X' appears in the appropriate
box. My problem is that when rooms are added they appear in the query but
not in the report based in that query. I hope that this is clearer. It isn't
imperative that the dates be down the left, they could be across the top and
the rooms down the side.

Any suggestions on how to achieve this would be most welcome. I'm quite
happy to split the reports into seperate rooms or two lots of dates,i.e. 1-
38 days & 39 - 76 Days and will entertain any ideas to work around it.

I thought, possibly,it could be achieved by macros hence the new posting to
that newsgroup.

Cheers,

 

[SharkSYA]

SharkSYA wrote:

Here is what I did. You can check this out...take about 2 minutes. I created a
table with the following fields
RoomID Autonumber, Primary key
Room number
RoomDate Date/Time

I then added a few records for a couple off dates. I entered rooms 1,2,3. for
a few rows and then randomly entered a few dates for each room. Here is my
query.

TRANSFORM Count(Table4.RoomID) AS [The Value]
SELECT Table4.RoomDate, Count(Table4.RoomID) AS [Total Of RoomID]
FROM Table4
GROUP BY Table4.RoomDate
ORDER BY Table4.RoomDate
PIVOT Table4.Room;

When you run it, you get dates down the side, room numbers at the top, and if it
is to be occupied for that date then it has the number 1 in the column, else it
is null.

You can format it to check for the value of 1 or Null and put an X in the
report. No need for a second query.

There is a potential problem with your report if you have PREDEFINED columns at
the top for each room. Lets say you have rooms 1,2, and 3. Room 2 has no
occupancy within the date range. Your columns would be Date, Room1, Room3, no
number 2.

In this case, you might want to create another table, Call it RoomBlank. In
this table, you'd want to create the same structure, and create 76 rows (to
match the number of rooms you have). Enter all of the room numbers BUT leave ALL
of the dates blank.

Now create a query that collects to rooms within the date range and union it to
RoomBlank. You are now assured that all rooms will be listed for your report
whether or not there is an occupancy within the date range. It is with this
query you'd create the crosstab. When you run the report, you'd want to exclude
the row where the date is blank (null)

Thanks for that. I'll give it a try.

Cheers,