How to summarize records on one page?

[Muneer Mikel]

Hello,

I want to make a report that summarizes how many hotel rooms are reserved
per night. I need to modify the following code:

Date = CheckInDate

Do Until Date = CheckOutDate

Select case Date

Case Date1
TotalRooms1 = TotalRooms1 + 1
Case Date2
TotalRooms2 = TotalRooms2 + 1
-
-
End Select
Date = Date + 1
Loop

Thank you

 

[Duane Hookom]

Why don't you show us your records (few samples) with how you want them to
appear in your report? I can't believe that code is the ideal method for
this.

 

[Muneer Mikel]

Duane,

In the query I have:

Name CheckInDate CheckOutDate HotelName
John 1/7/2005 5/7/2005 Hilton
Smith 1/7/2005 4/7/2005 Hilton

the summary report should show:

Date Room Nights for
Hilton
1/7/2005 2
2/7/2005 2
3/7/2005 2
4/7/2005 1
5/7/2005 0
6/7/2005 0
7/7/2005 0



Thank you
Muneer

 

[Duane Hookom]

I would create a table of dates with a single date field (tblDates.TheDate)
and add a record for each date for you entire range of dates and beyond.
Assuming your query is named qselReservations, you can then create a query
like:

SELECT Hotel, TheDate, Count(GuestName) AS RoomNightsFor
FROM qselReservations, tblDates
WHERE TheDate Between [CheckInDate] And [CheckOutDate]
GROUP BY Hotel, TheDate;

BTW: Name is not a good name for an object since every object has a name
property.

 

[Muneer Mikel]

Duane,

First, I have to create a link between the tblDates and the query so I could
select fields from both. I'll work on that and I'll let you know of the
results.

Thank you
Muneer

 

[Duane Hookom]

The query that I am suggesting doesn't have a join between the query and
tblDates.

 

[Muneer Mikel]

Duane,

The result of my query "qryRoomSummary" is:

Hotel CheckOutDate CheckInDate RegistrationNo

Hilton 17/09/2005 13/09/2005 3
Hilton 17/09/2005 13/09/2005 1
Hilton 17/09/2005 14/09/2005 2

When I used your code in the record source for my report "rptRoomSummary"

SELECT [qryRoomSummary].[Hotel], [Dates].[TheDate],
Count([qryRoomSummary].[RegistrationNo]) AS RoomNights FROM qryRoomSummary,
Dates WHERE ((([Dates].[TheDate]) Between [CheckInDate] And [CheckOutDate]))
GROUP BY [qryRoomSummary].[Hotel], [Dates].[TheDate];

I got this:

Date Room Nights
13/09/2005 2
14/09/2005 3
15/09/2005 3

It seems it is working, however I'm missing the date "16/09/2005". I tried
to use ([CheckOutDate] + 1) in the code but is didn't work!

Thank you
Muneer

 

[Duane Hookom]

Does your date field also contain the time portion?

 

[Muneer Mikel]

The problem was I didn't have the date "16/09/2005" in the "Dates" table
(Sorry ) After I entered all the date ranges "13/09/2005 - 20/9/2005", I
had the CheckOutDate value also on the report "17/09/2005". I solved the
problem by using "CheckOutDate - 1" in the query.

It is working fine now. Thank you so much Duane for your help, I appreciate
it

Another thing, can the date ranges in the "Dates" table be based on two
values entered by the user "StartDate" and "EndDate".

Also, if you don't mind, could you please tell me why you thought at the
begining that my code wasn't the ideal method of solving this problem. I
spent almost two weeks on this, and I thought that my code is the way to go.
What made you think of the other way. I just want to think your way in the
future.

Thank you
Muneer

 

[Duane Hookom]

SQL is generally more efficient and portable. Don't get me wrong, I love to
code but will toss it all out if there is a pure SQL method. A date table
comes in handy for lots of stuff like this.

You can use a criteria against the date field. I hate parameter prompt
queries. Consider using controls on forms to set your criteria values.

 

[Muneer Mikel]

Thank you so much

Muneer

 

[Muneer Mikel]

Duane,

What if the "Dates" table was based on "StartDate" and "EndDate" values on
another table that has only one record?!!

Thank you
Muneer

 

[Duane Hookom]

That would work as long as the other table has just one record. Just add it
to a query and don't join it to any other table. Use the two fields with a
"Between ... And ..." in the criteria of the date field.

 

[Muneer Mikel]

Thank you so much Duane.

Muneer

That would work as long as the other table has just one record. Just add it
to a query and don't join it to any other table. Use the two fields with a
"Between ... And ..." in the criteria of the date field.

 

[Muneer Mikel]

Duane,

I'm sorry I guess I wasn't clear in my last question. What I meant is:
In my "Dates" table I have for example 10 records (dates), I enter them
manually. These dates will be different for next year. I don't want the
client to update them and enter new ones, I want this to be done
automatically. In my other table "Session", I have only one record that has
the two fields in it "StartDate" and "EndDate". I was thinking of a way to
update the "Dates" table using the dates between The "StartDate" and
"EndDate". The "Session" table gets updated through a form that has 5 fields
on it (including "StartDate" and "EndDate"). For the following year these
fields get deleted, and new ones get entered.

Thank you
Muneer Mikel

 

[Duane Hookom]

You are asking me if something will work. I wouldn't know without creating
all your tables and queries etc. Please test this yourself and then report
back.

 

[Muneer Mikel]

Duane,

I did exactly what you said in your other email. I added the table "Session"
into my query "SELECT [qryRoomSummary].[Hotel], [Dates].[TheDate]......", and
selected the fields "StartDate" and "EndDate" from table "Session" and put
the (Between [CheckInDate] and [CheckOutDate] in the criteria for both fields
and it gave me this message:
(You tried to execute a query that does not include the specified expression
'Session.StartDate='Between[CheckInDate]" And
Session.StartDate=[CheckOutDate] And Session.EndDate='Between[CheckInDate]"
And Session.EndDate=[CheckOutDate] as part of an aggregate function)
I did test this many times, then I came up with another way to update the
"Dates" table. I created a form (based on the "Dates" table) where the user
enters the 10 dates for the current year, then for next year updates the 10
dates with the new ones. It worked fine, however the client dosen't want the
user input, and wants the program to enter the new dates into the "Dates"
table.

Thank you
Muneer Mikel

 

[Duane Hookom]

My dates table had all possible dates in it that might ever get used. My
solution was built on this assumption.

 

[Muneer Mikel]

Duane,

Yes, I knew that, that's why I send you the other message to clarify my
question. Anyways, I would like to thank you so much for your time and
effort, I really appreciate your help

Regards,
Muneer Mikel

 

[Duane Hookom]

You can create a table with all dates and then just use a query to limit the
returned dates to those dates that fall between specific start and end dates
or relative to today's date.