Monthly Query Query

Hi again! I want to create a query that will generate a monthly report. Problem is, I have NO idea how to do this, really. I was hoping to get some help.

Okay, here's what's up: Each employee has a Hire Date and a Probation Date. The hire date is when they were hired into the agency, and the probation date is the date they started on probation, which lasts a year. Once an employee is hired, their probation date and hire date is the same, but if they were to change jobs within the agency, their probation would start all over, but their hire date would remain the same.

When an employee is on probation, they get a 3-month, 7-month, and 11-month evaluation, which is based on probation date.

When an employee is not on probation, they get a 6-month and 1 year evaluation (based on hire date).

The report should generate who needs to receive evaluations in the current month and what type of evaluation it is.

I think this is what I want to say, but I don't know how to say it:

If ProbationDate is less than 1 year before today's date, then the evaluation is probationary.

In that case, if Probation date (month) is the current month minus three, then it's a 3-month eval. If probation month is current month minus seven, it's a seven month, etc.

If Probation Date is more than 1 year before today's date, then the evaluation is Annual/Semi Annual. If hire date's month is the current month minus 6, it's a Semi-Annual evaluation. If it's equal to the current month, then it's an annual evaluation.

I think it may be even better to do both of these things separately and generate two separate queries, as the two different evaluation types are dealt with by two different people. However, it might be nice to generate something that lists them all, in case they need it in the future.

If you've paid attention this long, I thank you very much. If you are able to help, I thank you even more.

--Sara

You'll need to store whether an employee is on probation Yes/No.

Do you store this?

Do you store startdates of jobs employees are assigned to?

RV

Yes, we store whether the employee is on probation.

When you say startdates, do you mean a date other than the probation date or the hire date? We store a ProbationDate and HireDate. StartDate is the initial hire of the individual. Probation date is the same date, unless they switch jobs within the agency.

Thanks!

--Sara

?

Once an employee is hired, their probation date and hire date is the same, but if they were to change jobs within the agency, their probation would start all over, but their hire date would remain the same.

Click to expand...

So you are really only going off of probation date then, right?

You could do a couple of things

1) Have a seperate query for each type of probation period (X months and filter personnal by on probation or not). Use DateDiff() to figure out difference between todays date and proation and check against that query's set probation preiod.

2) Have two queries (On Probation and Not), Calculate date diff in terms of months (e.g not probation = 6 and 12 months). You'll need fo format the probation date as format([ProbationDate],"mm/yyyy"), not exactly sure on that. Then do a datediff() using the formated ProbationDate and a formated Now(), and use criteria (If datediff = 6 OR datediff = 12) to show all those due for an evaluation.

Well, I'm not sure... Probation date is only the date used for the probationary evaluations. The hire date is used for the others (annual and semi-annual).

I'd like the query to tell me which type of evaluation it is. (If the probation month is three months before the current month, it will tell me it's that type of evaluation.)

Is all this possible?

--Sara

How's this?

SaraMegan,

Try this...

One table
tblPerson - Person, Probation (Yes/No), ProbationDate)

Two queries
OnProbation
NotOnProbation

Lemme know if this is what you are trying to accomplish.


It only takes into account the difference in months (Feb vs Sept but doesn't take into account the Day of Probarion Feb 23rd), I think.

That's all I have time to try now. Post back and if it needs tweaking, I'll give it a whirl!

Thanks, Cosmos. I finally just got a chance to try out what you posted, but it won't open. Are you using Access 2000? I should have said - I'm using Access97. Thanks, though.

Anyway, still having trouble with this... so if anyone has any more thoughts, it'll be my major project for the morning (and probably the day,) so I'll be checking back frequently. Thanks a lot. You guys are great.

--Sara

Cosmos - I just found someone with Access 2000 and was able to open your sample. I don't know if it's quite what I want, although the ElapsedMonths section of the query I'm sure will be helpful.

As I said, the probation month is not the only factor. It is only a factor if the employee is on probation. Otherwise, the HireDate is the date we are looking at. (Jobs change frequently around here, so people almost never have the same probation month as they have hire month.)

Is there any way to state a query as I did in my original post? Is it unclear? If it is, please post back with questions and I will do my best to clarify.

Thanks.

--Sara

**EDIT** - I have a check-box for whether the person is on probation or not. As of now, it gets checked off manually... is there a way to have it just be automatically checked off if the probation date is within a year before today's date? Does that help with the query problem? Would it just work better to keep it the way it is? Thanks! **End Edit**

Flow Logic

SaraMegan,

Can you post a flow logic for what you are trying to accomplish?

e.g.
Look at ProbationStatus
If ProbationStatus = True Then
....
If ProbationStatus = False Then
Look at HireDate if Date() - HireDate > 6 months THen
....

That's help me see what is the best way to approach your problem. (or at least try my best to do so!)

Okay, not quite sure how to do this, but I'll do what I can.

If date() - ProbationDate < 12 months (or 1 year?) then ProbationStatus = yes.

If ProbationStatus = yes and date() - ProbationDate = 3 months then EvaluationType = 3 Month

If ProbationStatus = yes and date() - ProbationDate = 7 months then EvaluationType = 7 Month

If ProbationStatus = yes and date() - ProbationDate = 11 months then EvaluationType = 11 Month

Now, here's the thing... For these last two, it should not even look at the year... I have no idea how to word that... Here goes nothing

If ProbationStatus = no and date() - HireDate = 6 months, then EvaluationType = Semi-Annual

If ProbationStatus = no and date() - HireDate = 12 months, then EvaluationType = Annual

Thanks again. Let me know if you have more questions!

--Sara

One more thing,

Are you taking into account only the difference in months (Feb vs July)?? Or exact Dates (Feb 2nd 2002 and June 30 2002)??

to determine probation status, it needs to look at the year, but otherwise should only look at the month.

Try this and see if it does what you want.

Wasn't sure if it was better to break it into two queries or not but I'll leave that for you to decide.

Access 97 Version

EDIT:

If date() - ProbationDate < 12 months (or 1 year?) then ProbationStatus = yes.

Click to expand...

Please note that I've made ProbationStarus a calculated field instead of part of a table, since based on your statement, it looks like ProbationStatus should be calculated and not a static field which you have to change manually.

Tired, must go home!!

Try this and see if this gets it right!

New query fields!

MHire: Just the month portion of the HireDate
MProbation: Just the month portion of the ProbationDate
M2Date: Just the month portion of Date()


MonthProbation: Difference looking only at month NOT year

YearProbation: Difference in years
- In terms of days!
That is 09/05/2001 and 09/13/2002 is 01
and 09/15/2001 and 09/13/2002 is 01
09/13/2001 and 09/13/2002 is 00, if you want to treat this as one year you can just use [2Day]-([ProbationDate]+1).
- It checks to see if the ProbationDate is later than Date() and returns a text string.


ProbationStatus:
- Checks to see if YearProbation is a number
-- if not it returns the text string in YearProbation
-- if yes, the if checks of it is greater than 0
------If Yes -> ProbationStatus is No
------If No -> ProbationStatus is Yes

EvaluationType: Complicated Nested Iif Function
-Checks to see if
-- ProbationStatus = Yes and MonthProbation is 3 or 7 or 11
----If Yes then it returns MonthProbation and adds the text Months after the number
--ProbationStatus = No
----If MonthProbation = 6, returns text string "SemiAnnual"
----If MonthProbation = 12, returns Text string "Annual"

Please take a look at the result for person Test2.

Probably some more I can do with it, but gotta go home!

Lemme know if the logic is still incorrect or if you can think of any situation when this would return an incorrect result!

Hope this helps!

Forgot you use Access 97, Sara!

User entered constant used in query / Define Parameter Data Type

Sara,

I figured it out! You can do either using a form or by a parameter (??) in the query! It has to do with telling the query what the parameter is (Date, Number...). If you right-click (Access 2000) in the query design grid, choose paramters and type in the parameter (if you are refering to a textbox on a form, you'll need to enter it as you fo in the query - [Forms]![FormName]![txboxName]), you then need to choose what type of data it is.

The problem in our query was [2day]-[ProbationDate]. That was causing the error was Access didn't know what data type [2Day] was and hence couldn't perform the calculation. I think, since I just used that calculation to see if that gave an error, which it did. If you notice, all our other calculations follow that calculation.

Anyway, here's the file (Access 97) with 2 new queries and 1 new form.

Form
frmTxt2Day - enter a date and click the button to run the query txt2Day.

Query.
txt2Day - Takes the date from the form and uses it as 2Day.
inputbox- Asks user for a date before running the query.

WOOHOO!!!

I am so excited! Thanks to you, Cosmos, and the people who helped me on the other post, I finally got the stupid thing to work! I've been ready to kill myself over this for about a week now, and no it finally works! Thanks so much!

I love this forum!

--Sara