Generating a table given a user input range (dates), then attaching 24H (column 2) to each date

Question

I had asked this prior, but I think I worded it poorly or perhaps not clearly enough. I'm pretty new to Access and SQL, but I'm pretty adept with computers. I've read a few books/websites on Access/SQL, but I'm finding this problem a bit difficult. This would be a lot easier if it was a simple SELECT range, but the scenario wants me to generate a table from a date range (window prompts), then attach 0-23 to each date. It repeats the same date for each hour associated with it.

I'm pretty lost in generating the incremental dates let alone attaching the hours.

Example output given sample input 1/01/2009, 7/30/2009

Using MS Access 2007

Column 1 (Date)   Column 2 (Hour) 

01/01/2009           0

01/01/2009           1

01/01/2009           2

01/01/2009           3

01/01/2009           4

01/01/2009           ... continues to 23

01/01/2009           22

01/01/2009           23

01/02/2009           0

01/02/2009           1

01/02/2009           2

01/02/2009           3

01/02/2009           4

01/02/2009           .. continues to 23

01/02/2009           0

01/02/2009           1

.................................. continues until the end date supplied by the user

07/30/2009           22

07/30/2009           23

Answer 1

Here's what I would do.

I would create a date table - easiest way would be to do an Excel autofill of dates for as many years as you want and then copying that into your table. The table should have an ID and the date. For example:

ID        Date
==========================
1         2010-01-01
2         2010-01-02
....

I would also create an hours table - again with an ID and an hour column (integer).

ID        Hour
=======================
1         0
2         1
...........

Then create a query. If you are using design view, add your two new tables. Add the date column and the hours column. In Criteria for the date field, put "Between [StartDate] And [EndDate]". Specify that the date column and hour column should be sorted ascending. Change it to a Make Table query and run it. You will be prompted for a start date and end date. That should do it!

Here is the generated SQL if it helps:

SELECT Dates.ShortDate, Hours.Hour INTO DatesHours
FROM Dates, Hours
WHERE (((Dates.ShortDate) Between [StartDate] And [EndDate]))
ORDER BY Dates.ShortDate, Hours.Hour;