[MS Access 2007] Prompting user for 2 dates and generating table with 0-23 hours for date range

Question

I need to prompt the user for 2 dates, e.g. 1/1/2008 , 2/5/2008 , and generate a two column report with all 0-23 hours for each date in the range. The date being in the first column and the hour being in the second column.

1/1/2008 0

1/1/2008 1

1/1/2008 3

[...etc]

2/4/2008 23

Answer 1

You could create an hours table with 24 rows containing values 0-23, then do a cross join (no links) with each date in the range entered.

Answer 2

You can do something like that by doing a simple join on two temporary tables

SELECT tmpdate.day,tmp hour.time
FROM tmphour, tmpdate
ORDER BY tmpdate.day, tmphour.time;

tmphour containing a list hours 0-23 and tmpdate containing a list of dates