Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Help with recurring event

    Hi everyone
    I think this should be easy, but I can not figure out how to do it. I have a database which contains my personnel's names and the dates they started working. What I need to be able to do is to generate a report every 90 days from the date they started, not just once. I have tried enterring the expression [startdate]+ 90, [startdate]+180, etc into a query, but some of my personnel may be with me for 05 years or more, which would make for an overly big query that I would have to continuously modify to reflect the extra or lesser amount of time. Is there a simpler way to determine each 90 days from a specific date? Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    hi

    in your situation it may be worth setting up a field that is set as the review cycle. In doing this when youve done you initial 90 review you can then change it to 365 or what ever your poilicy will require. following this protocol will allow for an any time review pattern and stop your query calling everyone and giving you a huge work load added to this you have the initial 90 day review with a little work you can set up a field for start_date , initial_rview 90 days later and then set a a small work around that calls that employee for every x period after the last reviewed date

    rgds
    gareth

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Not quite sure

    Hey gareth
    Thanks for your reply. I am not quite sure exactly how to implement your suggestion, not an access expert. I understand setting up the field for an initial 90 day review, but after that how would I calculate the next 90 days automatically? Sorry if this is a dumb question, not an expert by any stretch of the imagination, just a gifted novice. Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    No problem, we're ALL learning!

    ok first in the table where you have your employee start date
    put in a new field we'll call it First_Review for this explaination.
    set the field so its a date/time then set as "short date" - this is done in the properties.

    now you are ready to fill in the empty records. To make it simple and cut out all the code for now we'll make a QUERY, do this in design view not in simple query wizard.

    Add the table to the query which has the start dates and First_Review date (i am assuming they are in the same table)

    In the Field section of the query use the drop down and put in there the field - First_Review. The table its in will go on its own in to the section underneath name table.

    in the section "Update to" place >[121]![Startdate]+90
    this is [the table name where the startdate is]! [fieldname]+90

    when you save the query give it a name that means something about what it will do

    now if you run the query it will take a while but it should populate the field named "First_Review" with dates 90 days greater than the original start date ( you can of course use the same thing for dates to be reviewed 90 days after the first review or as many days as you want to spread the reviews)

    once this is done and you are happy with the date spread you can make a query the is set to look for Reviews due between date_A and date_B

    that will find those forthcoming and allow you to plan ahead

    I hope Ive made this as clear as possible for you?

    if you've any questions ask -

    regards-good luck
    gareth

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Opps

    just to finish off the query operation for the upate to speed future updates in the "Crieria" section if you place "Is Null" the query will look for dates NOT filled in for the "First_Review" so it will not try to re-write all the dates everytime you run the query

    And to top it off - place a command button on the form that you enter thenew starter details in to so that you can finish entering and "Click"- youve updated to the last person


    regards

    gareth

  6. #6
    Join Date
    Mar 2004
    Posts
    3

    Wink Expediant workaround, ain't pretty but it worked

    Hey Gareth
    'Preciate all your help. What I ended up doing, as my supervisor was breathing up my butt to get this rolling, was just made a select query with DateAdd expressions, only for 90, 180 and 270 days, then made a make table query that used DatePart expressions to pull the month value so they matched a listbox on the form. After that, its all Outputed to a Excel spreadsheet, as our ever so knowledgable IT department doesn't allow us to send MDB or even the generated report through our email servers, but they will let spreadsheets through. Makes sense, eh?
    Anyways, thanks for your help and I am going to implement your suggestions when I have three or four free minutes to work on it.
    Dean

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •