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.
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
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.
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 >![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?
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
'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.