Help Needed with Analysis of Date Ranges

[jbs001]

I am working on a hotel booking system using MS Access. The room bookings are logged in the database as "from" and "to" dates. I need to write a report which shows, for each room, how many days have been booked in each month. I can't get this to work when the "from" and "to" dates cross over a month end - e.g. from 28/01/2007 to 05/02/2007. How do I write a query which will put the 4 days correctly in the January total and 5 days in the February total?

Help much appreciated.

 

[traingamer]

try

...between #01/28/2007# and #02/05/2007#

You must use U.S. Date formats. See Access help

When you specify the criteria argument, date literals must be in U.S. format, even if you are not using the U.S. version of the Microsoft® Jet database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jbs001]

The 'between' criteria will return the correct number of days in a range - no problem. The question I want the system to answer is "If room A is booked from (in US format)#01/28/07# to #02/05/07#, how many days are in January and how many in February?" The 'between' criteria suggested just returns one number - not what is required. The answer I need in this example is:

Jan Feb
Room A 4 5

Sorry if I have not explained the issue clearly. I am new to the forum, but that is no excuse!!!!!

 

[SkipVought]

Hi,

The Between criteria will not change.

It's the SELECT statement that must be stated. Take a look at a Crosstab, Across Months.

Skip,
[sub]
[red][/red]
[/sub]

 

[traingamer]

Sorry, I completed mis-read the question.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[JPMontreal]

First, you will need a query like that (not tested), It assumes that customer doesn’t stay more then a month:
[blue]
SELECT
RoomN,
Month([From]) AS FromMonth,
IIf(Month([From])=Month([To]),DateDiff("d",[From],[To]),DateDiff("d",DateSerial(Year([To]),Month([To]),1)-1,[To])) AS FromDay,
Month([To]) AS ToMonth,
IIf(Month([From])=Month([To]),0,DateDiff("d",DateSerial(Year([To]),Month([To]),1),[To])) AS ToDay
FROM Books;
[/blue]
You save it, in this exemple as Query1
DateSerial(Year([To]),Month([To]),1)-1 gives the last day of the previous month.

Then, you make a union query :
[blue]
Select RoomN, FromMonth as TheMonth, FromDay as NumberDay
From Query1
Union all
Select RoomN, ToMonth as TheMonth, ToDay as NumberDay
From Query1
[/blue]
You save it, here as Query2

And last you do a sum query:
[blue]
Select RoomN, TheMonth, sum(NumberDay)
From Query2
Group By RoomN, TheMonth
Order By RoomN, TheMonth
[/blue]








Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[JPMontreal]

Sorry, there is an error in my first query
[blue]
SELECT
RoomN,
Month([From]) AS FromMonth,
IIf(Month([From])=Month([To]),DateDiff("d",[From],[To]),DateDiff("d",DateSerial(Year([To]),Month([To]),1)-1,[To])) AS FromDay,
Month([To]) AS ToMonth,
IIf(Month([From])=Month([To]),0,DateDiff("d",DateSerial(Year([To]),Month([To]),1),[To])) AS ToDay
FROM Books;
[/blue]

It should be:
[blue]
SELECT
RoomN,
Month([From]) AS FromMonth,
IIf(Month([From])=Month([To]),DateDiff("d",[From],[To]),[red]DateDiff("d",[From],DateSerial(Year([To]),Month([To]),1)-1)[/red]) AS FromDay,
Month([To]) AS ToMonth,
IIf(Month([From])=Month([To]),0,DateDiff("d",DateSerial(Year([To]),Month([To]),1),[To])) AS ToDay
FROM Books;
[/blue]

Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[jbs001]

Thanks. That pretty much worked after a bit of tinkering. I have got the answer now on a crosstab query but I need to total the columns to get it finished - how do you do that?

Thanks for your help so far. Much appreciated.

Merry Christmas.