Need current and next instance of a job

[andrewf10]

Hi,

I have around 50 jobs on a table. These have a 'start date' of various dates in a year. A field called 'complete' indicates whether a job has been closed off or not and a field called 'frequency' determines how often the jobs recurr (in days).

I need a query (which I'll run every night) which will examine all the incomplete jobs on the table and copy all this data to new jobs and change the 'start date' to the previous 'start date' + 'frequency'.

That in itself is not the problem. My problem is how to tell Access that when the current and next instances of a job exist, not to copy it a third time.

In other words, after the query runs there should always be 2 instances of the job on the table...current and next.

Can someone please advise me what I should do?!

Thanks in advance

 

[ColinEssex]

Thinking quickly, I would have a check number (1 and 2)

If the job has No1 its ok and if it has No2 its ok. If there's no No2 (for the job) then put one in

Col

 

[andrewf10]

Thanks Colin but not clear how this would work though. Do I need a seperate update query as well?