I am trying to get a count of hotel nights for an upcoming meeting, I have a table with each persons "Arrival Date" and "Departure Date" and a query with those 2 dates and the "Number of Total Nights" (used DateDiff formula). How do I find the number of rooms I need for each night?

Ex:
Arrival Date Departure Date Number of Nights
3/9/2014 3/14/2014 5
3/9/2014 3/13/2014 4
3/10/2014 3/11/2014 1

Number of Rooms needed for each night: (This is the part I cant figure out how to do???)

I generally start by creating a table (tbl_Numbers) which contains 1 field (lng_Number) and 10 records, the values 0-9.

I then create a query (qry_Numbers) that generates the numbers 0-99:
SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones.

With this query, I can generate a list of 100 consecutive days:

SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate
FROM qry_Numbers
WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]

Using this as a subquery, you can include your table and generate the count of records per day.

SELECT sq.MyDate, SUM(iif(T.ArrivalDate <= sq.MyDate AND T.DepartureDate > sq.MyDate, 1, 0)) as RoomCount
FROM (
SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate
FROM qry_Numbers
WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]
) as sq, YourTable as T
GROUP BY sq.MyDate

You could refine that further so that you are not comparing the dates in the MyDate query agains all dates in your other table by filtering the other table first. Something like:

SELECT sq.MyDate, SUM(iif(AD.ArrivalDate <= sq.MyDate AND AD.DepartureDate > sq.MyDate, 1, 0)) as RoomCount
FROM (
SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate
FROM qry_Numbers
WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]
) as sq, (
SELECT * FROM YourTable
WHERE YourTable.ArrivalDate <=[EndDate]
AND yourTable.DepartureDate >= [StartDate]
) as AD
GROUP BY sq.MyDate

0

snoopyswimerAuthor Commented:

I guess I should have mentioned that I am not great with coding. ;-)

I am going to try and mess with what you gave me and see if I can make it work.

Yes, each person has their own room. That looks like it will work. I actually have all my data in one table (tbl_main). Here are the fields in that table: FirstName, LastName, EmpId (unique number), Arrival, & Departure. Will that form / code work with just one table?

Sure.
The idea is the same.
Change the line in the sample to Set rs = CurrentDb.OpenRecordset("Select EmpId from tbl_main where arrival <= #" & Me.txtDate & "# and departure >= #" & Me.txtDate & "#;", dbOpenDynaset, dbSeeChanges)
Should git 'er dun

It's not a far stretch to put multiple boxes onto a form to run it for multiple entered dates.
It's also not a far stretch to turn it into a function that you feed date in, and get # of rooms out.
Or to dummy up a form that figured out the maximum number of days between [arrival] & [departure] and to do the calculation for each day -- that's more complex as you need a dummy table to bind to get sequential numbers -- like you suggested.
Which I could have done for the Asker if requested.

But the question was "How do I find the number of rooms I need for each night" Singular
I didn't read it as 'How do I find the number of rooms I need for each and every night" Totality

:)

But then I always think in terms of VBA recordsets as my weapon of choice -- yours is SQL.
I thought about suggesting DCount -- but Domain Aggregates are just evil.

Nick67

0

Featured Post

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I then create a query (qry_Numbers) that generates the numbers 0-99:

SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number

FROM tbl_Numbers as Tens, tbl_Numbers as Ones.

With this query, I can generate a list of 100 consecutive days:

SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate

FROM qry_Numbers

WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]

Using this as a subquery, you can include your table and generate the count of records per day.

SELECT sq.MyDate, SUM(iif(T.ArrivalDate <= sq.MyDate AND T.DepartureDate > sq.MyDate, 1, 0)) as RoomCount

FROM (

SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate

FROM qry_Numbers

WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]

) as sq, YourTable as T

GROUP BY sq.MyDate

You could refine that further so that you are not comparing the dates in the MyDate query agains all dates in your other table by filtering the other table first. Something like:

SELECT sq.MyDate, SUM(iif(AD.ArrivalDate <= sq.MyDate AND AD.DepartureDate > sq.MyDate, 1, 0)) as RoomCount

FROM (

SELECT DateAdd("d", lng_Number, [StartDate]) as MyDate

FROM qry_Numbers

WHERE DateAdd("d", lng_Number, [StartDate]) < [EndDate]

) as sq, (

SELECT * FROM YourTable

WHERE YourTable.ArrivalDate <=[EndDate]

AND yourTable.DepartureDate >= [StartDate]

) as AD

GROUP BY sq.MyDate