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 7 1614
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
"SharkSYA" <sh****@tw.uk> wrote in message news:40******@clear.net.nz... 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
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" <sh****@tw.uk> wrote in message news:<40******@clear.net.nz>... 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,
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om... 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 "SharkSYA" <sh****@tw.uk> wrote in message news:<40******@clear.net.nz>... 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,
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)
On Sat, 17 Jan 2004 12:37:33 +1300, "SharkSYA" <sh****@tw.uk> wrote: 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 wrote: 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
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)
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:iu********************************@4ax.com... 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)
On Sat, 17 Jan 2004 12:37:33 +1300, "SharkSYA" <sh****@tw.uk> wrote:
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'timperative that the dates be down the left, they could be across the top
andthe 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
tothat newsgroup.
Cheers,
"Salad" <oi*@vinegar.com> wrote in message
news:40***************@vinegar.com... 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,
--
SharkSYA This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tim |
last post by:
Hello,
Since this is a newsgroup about php & web related topics, I wanted to
share a new Search Engine Optimization tool that we just released. I
know many of you have non optimized dynamic...
|
by: Sam |
last post by:
Hello everyone,
I have a table, which contains a picture column, I put URL info into it.
"www.myweb.com/1.jpg..."
I want to show this picture in my crystal report, I find
some samples show the...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Richard Hollenbeck |
last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently...
|
by: CAD Fiend |
last post by:
Hello,
Well, after an initial review of my database by my client, they have
completely changed their minds about how they want their form. As a
result, I'm having to re-think the whole process....
|
by: Ron |
last post by:
I have a bunch of Crystal Reports (v9) published as WebServices and use a
ReportViewer to display the reports on the ASPNET page. Everytime we move
the reports from dev to production we have to...
|
by: PughDR |
last post by:
As the subject of this topic suggestions I am trying to find a way to
use ASP, SQL Server, Com+ and Crystal Reports 8.5 to Create Dynamic PDF
Reports Over The Web, but the only article I found that...
|
by: fjm |
last post by:
Hi all,
I have to give users a way to create dynamic reports. Can sombody give me some ideas please? My first thoughts were to create static reports and I'm thinkin' that is not as good as it...
|
by: creative1 |
last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |