Solved

How to find a count of each day between two dates

Posted on 2014-11-20
8
182 Views
Last Modified: 2014-11-20
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???)
                  
3/9/2014      3/10/2014      3/11/2014      3/12/2014      3/13/2014
2                      3                      2                      2                      1
0
Comment
Question by:snoopyswimer
  • 3
  • 3
  • 2
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

by:snoopyswimer
Comment Utility
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
Each person has a room to themselves?
Code on a form
Option Compare Database
Option Explicit

Private Sub txtDate_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select PersonID from tblStays where arrival <= #" & Me.txtDate & "# and departure >= #" & Me.txtDate & "#;", dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then
    Me.txtRooms = 0
Else
    rs.MoveLast
    Me.txtRooms = rs.RecordCount
End If

End Sub

Open in new window


Fire up a recordset of stays that a given date falls between arrivals and departures.
The count is the number of rooms required.

Sample attached
hotels.mdb
0
 

Author Comment

by:snoopyswimer
Comment Utility
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?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 500 total points
Comment Utility
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
0
 

Author Closing Comment

by:snoopyswimer
Comment Utility
This worked perfectly!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
I'm confused.  That only returns the value for a single day, that is not what you asked in your original post.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@fyed

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now