Create "virtual" data on query?

Vote count:

0

I hope the title is more or less alright, didn't know how to describe my problem.

What I would like to do is to create a timeseries of dates that are between to dates that the user can chose, without actually saving the dates to a table. Let's say it is 15th Nov 2014 and 16th Nov 2014 I would like to have a query that gives me:

[date]
11-15-2014 00:00
11-15-2014 01:00
11-15-2014 02:00
..
11-15-2014 23:00

Is there any way to create those dates with a query (and maybe a table containing the start date) only?

I thougt about something like

SELECT Dateadd('h', i, t1.start_date) as date FROM t1

and then some part saying "i" will be between 0 and DateDiff('h', start_date, end_date)

I know this would be easy if there where temporary variables availabe, which unfortunately is not the case on MS Access.

Right now I'm using a workaround, where I have a table containing the date value of each hour for a few years and where I use a LEFT JOIN ... WHERE date BETWEEN, but I don't like this "dirty" trick much.

Thanks in advance!

asked 2 mins ago

frrrt

1

Create "virtual" data on query?