Results 1 to 4 of 4

Generating Recurring Tasks.

  1. #1
    Jamesamorris is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    2

    Generating Recurring Tasks.

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,058
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    Jamesamorris is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    2
    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

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,058
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 30
    Last Post: 06-26-2011, 09:47 PM
  2. Form using requerys and report generating
    By kroenc17 in forum Forms
    Replies: 1
    Last Post: 09-22-2010, 06:12 AM
  3. Generating Report from Form
    By mwabbe in forum Reports
    Replies: 8
    Last Post: 08-30-2010, 11:25 AM
  4. Generating stats - newbie question
    By FavouredEnemy in forum Reports
    Replies: 3
    Last Post: 04-15-2010, 07:11 AM
  5. Reading outlook tasks from Access
    By Bill_dom in forum Import/Export Data
    Replies: 0
    Last Post: 08-07-2008, 05:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums