Auto create record based on criteria and date in another record

[hom370]

I have a db where each record is a certain task to be done. Each record
includes the date the task should be completed. Some of the tasks are one
time only but some are recurring. I have been having to create a new record
for every task even the recurring ones, but I have decided to try to automate
creation of the recurring tasks.

I'd like to have it so that when I set up a task I can select from a drop
down box that this task will not recur or that it will recur every week,
month, etc, and (here's the part I can't figure out) then based on the
selection it would create a new record of the same task but with the proper
due date based on which one of the recurrence options was selected.

For example, if the original task was:
Fax weekly expense report
Due 9/26/07
Repeat: weekly

I'd like for it to, on 9/26 or 9/27, automatically create this record
Fax weekly expense report
Due 10/3/07
Repeat: weekly

I don't want the same record to be changed to show the new date; I need to
have a record for each task.

 

[KARL DEWEY]

Several questions on your recurring task. Do you want the new record to be
create even if the old task was not completed? Do you want the new record
due date to be based on last due date or date last completed?

 

[hom370]

Thanks for your reply.
I do need the new task (record) to be created even if the old one was not
completed and I need the new due date to be based on the original due date.
So if a recurring task was set to be weekly and due today, then either today
or tomorrow I would want a new task to be created and the new task's due date
would be next Wednesday.\
Thanks again.

 

[KARL DEWEY]

The query below is based on this table and the sample data. The interval is
Access standard DateAdd such as d-day, w-week, m-month, q-quarter, etc. used
with the multiplier. If a task is marked as OneTime then it will not create
a new record the next ime the query is run. You can add/de;ete field to/from
the table/query as you need.
Task EqNum MaintStart DoneDate MaintCode Mileage OneTime Interval Multiplier
Brakes Chev-3 03/09/2008 0 No m 6
Oil Ford-1 01/02/2006 8:05:00 AM 01/02/2006 2:35:00 PM 1 30000 No m 3
----------------
INSERT INTO MaintItems ( Task, EqNum, MaintStart, DoneDate, MaintCode,
Mileage, OneTime, [Interval], Multiplier )
SELECT MaintItems.Task, MaintItems.EqNum,
DateAdd([Interval],[Multiplier],[MaintStart]) AS NextMaint, Null AS Done,
MaintItems.MaintCode, Null AS Miles, MaintItems.OneTime, MaintItems.Interval,
MaintItems.Multiplier
FROM MaintItems
WHERE (((MaintItems.OneTime)=0))
GROUP BY MaintItems.Task, MaintItems.EqNum,
DateAdd([Interval],[Multiplier],[MaintStart]), MaintItems.MaintCode, Null,
MaintItems.OneTime, MaintItems.Interval, MaintItems.Multiplier
HAVING (((Max(MaintItems.MaintStart))<Date()));

 

[hom370]

I laid out your example table so I can try to understand the query language
and what it's doing.
......I think I'm going to have to look at it for a little while before I'll
understand it, haha. Thanks a lot. I'll get back to you on how it's going.

 

[hom370]

I've laid out your example table so I can try to understand the query and
what it's doing. I think it'll take me a little while, haha. I'll let you
know how I'm doing with it. Thanks a lot for your help.

 

[hom370]

In trying to understand what you're doing in the query I have a question
about the SELECT statement. Why does it select nulls for Done and Miles but
not any of the others?
And in the GROUP BY statement, why include nulls there?
Also, what does the GROUP BY statement do in this query?
Thank you...

 

[hom370]

I think I see what the nulls in the SELECT statement do: they write nulls in
those places to the new record. yes? I can be slow, but I keep trying.
haha.