Append Query to Create Repeated Records in Table

[bmsc]

I am trying to create an append query (or whatever would work) that
will allow the creation of repeated records in a table.
For example, I have a table with these fields (1st row) and example
data (2nd & 3rd rows):
event startdate freq
Sweep Driveway 10/03/05 12
Wash Car 10/08/05 4

The "freq" field is the number of times in a year the event should
repeat. So, what I need is to create records in a table (which only
needs "eventdate" and "event" fields) for the next year that would look
like this:
eventdate event
10/03/05 Sweep Driveway
10/08/05 Wash Car
11/03/05 Sweep Driveway
12/03/05 Sweep Driveway
01/03/06 Sweep Driveway
01/08/06 Wash Car
02/03/06 Sweep Driveway
...

This would continue for the full 12 months from the earliest
"eventdate".

Any ideas? Should this be done in VBA somehow?

Thanks for the help!
-B.

 

[Guest]

Why not use
Event LastDate Interval
Sweep Driveway 10/03/05 1
Wash Car 10/08/05 3

Use a query to calculate next due date --
DateAdd("m", [Interval],[LastDate])

Then run update or append query if you are tracking history.

 

[bmsc]

Thanks for the reply.

I was able to use DateAdd after creating two tables for frequencies,
which I needed anyway for choosing frequency during data entry. I
needed to have more than just one occurrence to repeat the event, kind
of like what programs like Calendar Creator would have to do. One of
the frequency tables just had the frequencies and their interval
strings. The other table had the FreqID from the first table and the
frequency iterations (52 records for weekly, 1 for yearly, 12 for
monthly, etc.). That way when I run a regular select query with the
main table of events and both frequency tables as sources, the records
automatically were created for the entire year. If I wanted to do more
or less than one year, I would just change the number of iterations in
the second frequency table. It works quite well.

I can go into more detail if anyone searching for this would like more
info.

Thanks again.
-B.