Return all dates within a date range as separate result

[swicked]

:banghead:I have a training database (Access 2007) in which a CLASSNAME is listed with both a STARTDATE and ENDDATE. I would like to create a query that would return all dates from within the date range - to create a calendar type result

For example:
TABLE=DATE

CLASSNAME STARTDATE ENDDATE
NEWHIRE 01/15/2013 01/20/2013

I would like to return

NEWHIRE 01/15/2013
NEWHIRE 01/16/2013
NEWHIRE 01/17/2013
NEWHIRE 01/18/2013
NEWHIRE 01/19/2013
NEWHIRE 01/20/2013

Any help/example would be greatly appreciated

 

[AccessMSSQL]

You need to create another table called Calendar or Years and load every date for each year into it.

So you would have a Calendar table with this data in it:

cdate
1/1/2013
1/2/2013
1/3/2013
1/4/2013...
etc.. for 365 days of the year.

Then you just need to create a query: select classname, startdate, enddate from Date left join calendar on date.startdate = calendar.cdate and calendar.cdate <=Date.EndDate
where classname = 'NEWHIRE'

This query should work but only for one class at a time.

 

[AccessMSSQL]

Oops..sorry. the query above is not correct. Here is a revised one:


SELECT Date.ClassName, CALENDAR.cdate FROM DATE, CALENDAR
WHERE (((ClassName)="NEWHIRE") AND ((Calendar.cdate) Between [DATE].[StartDate] And [DATE].[ENDDate]));


NOTE: I would not name your table DATE. This is a reserved word in Access so you should call it something else.