How to create an Access query containing each date in a calendar range?

Question

I have an activities table of activities records, for example:

 ID | Date | Activity | Participant ---+------------+----------+ 1 | 11/30/2011 | Skiing | Alice 2 | 11/29/2011 | Diving | Gary 3 | 10/15/2011 | Running | Therese 

and I would like to ask for a certain date range which days had activity and which didn’t, for each participant, for example, for 11 / 29-11 / 30,

 Date | Activity | Participant ------+-----------+------------ 11/29 | | Alice 11/30 | Skiing | Alice 11/29 | Diving | Gary 11/30 | | Gary 11/29 | | Therese 11/30 | | Therese 

My current plan is to make a table of allDates all dates in the year, take a cartographic query for the product cartDateParticipant :

select date,participant from allDates,(select distinct participant from activities) as participants

and left attach this request to activities :

 select a.date, c.activity, c.participant from allDates a left join cartDateParticipant c on a.date=c.date and a.participant=c.participant 

which will work, but that means I have to keep a date table for any possible date range entered by the user. Is there a way to generate a date sequence on the fly in Access without having to store it as a table? Or is there a better way to write this query?

EDIT: The client finally relented and decided that no empty lines were needed, so I just wrote it as a standard request.

Answer 1

I dare to answer, since no one else has. Creating a date table may not be particularly cool or smart. However, it is easy to understand, and therefore easy to implement and maintain. I would stick to this method and say: “No, there is no better way to write a query” using MS Access.