Generating Recurring Tasks.

[Jamesamorris]

Hi All, I am just getting to grips with Access 2010... so far so good but I am having some difficulty finding a way to sort out the following problem ....



I have a database with many tables and queries but for this issue I will concentrate on .

CLIENTS
CALL_RECORDS_TEMP_SCHEDULE
CLIENT_CALLS_MASTER.

What I need to do is the following:

in Client_Calls_Master I have ( One to Many Relationship with clients)

Client Ref:
Call Type (e.g Domestic Help, Gardening Etc)
Call Start Time(Time)
Call End Time(Time)
Monday (Y/N)
Tuesday(Y/N)
Wednesday(Y/N)
Thursday(Y/N)
Friday(Y/N)
Saturday(Y/N)
Sunday(Y/N)

I know what I want to do but dont know how to do it.......

Ideally I want to enter a start date and an end date and have a routine go through the client_calls_master and insert a record into the temp_scheduletable eg... 1st Nov to 30th Nov client 1 has a call every tuesday and thursday so I would like to to create a call record for november on 2,9,16,23,30 4,11,18,25 so thats calls or however many a particular client needs,

Any ideas greatfully received.

James

[pbaldy]

I do something similar in a couple of apps. I generally automate it so that on one day the next day's records are automatically added. In one app where they have to plan far ahead I do a month at a time, two months out (on Jan 1 it adds March). I've got to run out in a minute so this will be a fly-by. You'll need a loop using your start/end dates and a variable. Within the loop, determine what day of the week it is. Use that and the current date in SQL built and executed within the loop:

INSERT INTO YourTempTable(...)
SELECT ...
FROM MainTable
WHERE Monday = True

where "Monday" is coming from your determination of the current date in the loop.

[Jamesamorris]

Hi Paul

Thank you very much for the reply... I think I have that now just not sure how to get it to say create a record for every monday in the selected date period.........ie

Start Date 1/11/10 End Date 30/11/10 for monday I would like it to generate 5 records each one dated on a monday i.e 1st , 8th, 15th,22nd, 29th and so on for Tuesday etc etc etc. At the moment I am manually creating the records which is becoming quite cumbersome as i generate 400 call records a month :-(

Any ideas greatfully recevied.... I need to brush up on my programming skills!

James

[pbaldy]

That would be handled by the loop I mentioned. It would cycle through each date in your selected time frame and add records for each date as appropriate. The SQL would use the date variable in it to add the appropriate date.