Show Data For Each Date Between Two Dates

Another Utter Access reader posted a question about displaying data for each date between a start and end date entered when running a query.

The goal was to take a Reservations table (see image below) where each record has a start and end date and generate a record for each reservation for each date. This could then be used to sum up the number of room reservation days as, perhaps, a performance measure for the business or just to get a daily count of reserved rooms.

I then generated a “YearDates” table that has a single field “DOY” (day of year) which is each day of the year. Some VBA code can quickly generate any number of years of dates for this table. I am going to use this table to join to the Reservations table in a query so that I can end up with a record for each date and reservation.

The SQL WHERE clause needs to select appropriate records based on the user entering a Start Date and End Date when running the query. At least one day of the reservation must be between, or equal to one of, the Start and End dates of the reservation.

The way I joined the RESERVATIONS and YEARDATES tables together is unusual in that it does not join on a field in each table with equal values but instead joins a range of rows from the YEARDATES table for each RESERVATION ROW.

I used a comparison in the SQL WHERE clause to limit the resulting output records to those between the Start and End date that the user enters when running the query. In the above image you can see the SQL and the results of running the query where I requested reservations for the week of 1/14/2009 to 1/20/2009.

There are pros and cons to this approach.

PROS:

– no VBA coding needed

CONS:

– have to generate a table of dates. This is a one-time activity but some developers don’t like using generated tables.

– query performance may be an issue with a really large number of records in your tables (3 years of reserverations in a 50 room hotel could generate 50,000 records).

Here is the SQL in a code block that you can copy:

SELECT 
  YearDates.DOY, Reservations.BookingID, Reservations.NumRooms
FROM 
  Reservations 
INNER JOIN 
  YearDates 
ON 
  (YearDates.DOY >= Reservations.StartDate) AND (YearDates.DOY <= Reservations.EndDate)
WHERE 
(
  (YearDates.DOY)>=[Enter Start Date] 
  AND (YearDates.DOY)<=[Enter End Date]
)
AND
(
 ((Reservations.StartDate)<[Enter Start Date]) 
    AND ((Reservations.EndDate)>=[Enter Start Date]) 
 OR ((Reservations.StartDate)=[Enter Start Date]) 
 OR ((Reservations.StartDate)=[Enter End Date])
 OR ((Reservations.StartDate)>[Enter Start Date] 
     AND (Reservations.StartDate)<=[Enter End Date]) 
)
ORDER BY 
  YearDates.DOY, Reservations.BookingID;
            

It would be easy to create a cross-tab query with the results of my query to get the data in the format you displayed in your post. I’d love to hear about alternate solutions and opinions from other members on this post.