I want my mommy..... (1 Viewer)

[riverstar5]

Ok, I'm done crying... Can someone please, please help me.

I am learning Access and I have encouraged my department to use Access to create data bases that will hold information about our employees. I have set up a lot of tables and forms however I am so stuck with Queries.

I want to create a query that will give me the employee's evaluation due date, however I am not understanding the concept. When I go to the layout I get confused and I'm not sure as to what fields I create the formula in. If someone can be so kind as to drawing this for me because when I try to read it from resources I just cant grasp it.
Please, please

 

[jzwp22]

You'll need to provide a little more info about your table structure (table names and field names) and your business process. Also, how do you capture the current and previous evaluations? What is the frequency between evaluations? Can the frequency vary by employee?

 

[riverstar5]

Thank you, thank you...

Ok so in my table I have names, addresses, contact information and then I have anniversary and evaluation date (last evaluation done). I need to calculate when is the evaluation due, by generating a date from the last evalutation and if possible a reminder when it is due 14 days in advance. Does any of that make sense?

 

[riverstar5]

Oh sorry, it will be if introductory 60 days and annually 1 year.

 

[sparks80]

Hi,

Let's say the evaluation date in your table is called "EvaluationDate"

In the query design view firstly you need to add your table. In the show table dialog box select your table and click "Add".

You can drag the fields in your table from the list at the top onto the grid at the bottom, so drag the EvaluationDate field to the grid.
In the Field row, and the next column type in the following:

NextEvaluationDue: DateAdd("d", 60, [EvaluationDate])

And in the next column:

DaysUntilNextEvaluation: IIF([EvaluationDate]>=DateValue(Now()), [EvaluationDate]-DateValue(Now()), "overdue")

 

[sparks80]

Do you have a way to tell if the evaluation is annual or introductory?

 

[riverstar5]

Yes I have a drop down list that I pick from that says annual or introductory.

 

[jzwp22]

So I assume that the 60 days is in reference to their anniversary date/hire date, so you need to first determine whether the employee is new. You can do that in either of 2 ways. The first would be to check to see if today's date is within 60 days of the anniversay date. The second and probably better way is to check to see if there is a date in the lastevaluationdate field. For a new employee that field will be null, so I'll go that route. The query would look something like this (SQL view):

SELECT tblEmployee.pkEmpID, tblEmployee.txtFName, tblEmployee.anniversarydate, tblEmployee.lastevaluationdate, IIF(isNull(lastevaluationdate), dateadd("d",60,anniversarydate), dateadd("yyyy",1,lastevaluationdate)) as EvaluationDueDate
FROM tblEmployee;

You will of course have to adjust the field and table names to your application

 

[riverstar5]

Hi jzwp22,

Im so sorry and I appreciate so much that your trying to help but I need steps, I'm not understanding what I'm trying to do without steps. I feel stupid...

 

[cookiegary01]

Hi Riverstar5.
On this site (top right side) go to "Video tutorials" , "Queries", and learn. Once you've done that and understand it, try to do what Sparks and jzwp informed you.

 

[jzwp22]

I'm not sure what version of Access you are working with but the basic functions to create a query are there in all versions

1. Create Query Design
2. Select the table you want to query (your employee table); this will show in the upper pane of the query design grid.
3. In the lower pane, select the fields you want to see in your query results.

That is how you create a basic query. You can view the SQL text of the query you created by switching to SQL View. The structure of a basic query will look like this:

SELECT fieldname1, fieldname2. etc.
FROM tablename

If you add certain criteria (not in this case), you would see a WHERE clause after the FROM clause

SELECT fieldname1, fieldname2. etc.
FROM tablename
WHERE criteria...

In your particular case, you will have to create the calculated field for the due date for the next evaluation. The following part of the query I provided earlier is the SQL text for that calculated field

IIF(isNull(lastevaluationdate),dateadd("d",60,anniversarydate),dateadd("yyyy",1,lastevaluationdate)) as EvaluationDueDate


The above uses 3 built-in functions available in Access (you can find out more about each one using the Help section). Those 3 functions are as follows

IIF(). This is basically a logical comparison IF..THEN. The basic structure is as follows

IIF(expression, what to do if expression is true, what to do if expression is false)

isNull() : tests the expression within the (), and if the expression is null a TRUE value is returned. If the expression is not null, then a FALSE value is returned. In your case, if the evaluation date field does not have a value (i.e. null), then that indicates that the employee has never had an evaluation and is probably new, so then the IIF() function looks at what it has to do in the true part which is dateadd("d",60,anniversarydate)

What the dateadd() function does is adds 60 days ("d") to the anniversary date which is the due date for that employee.

If there is a date in the last evalution date field, the IIF() function looks to the expression to do in the false part. That expression: dateadd("yyyy",1,lastevaluationdate) uses the same dateadd() function but adds 1 to the year ("yyyy") to the last evaluation date.

Now to add the IIF() function to the design grid is a little awkward, so I would recommend switching to SQL view and adding it manually to the end of the SELECT clause. You will have to replace the anniversarydate and lastevaluationdate field names with your corresponding names.

If you still have trouble, you could zip and post a copy of your database with any sensitive data removed/altered.

 

[riverstar5]

jzwp22,
Thank you so much for your time and patience, it was really nice of you to take the time to explain this to me. You don't understand how much that means to me, I'm going to do exactly what you instructed and I will let you know how it worked out.

I’m so sorry for not providing all the necessary information that you needed in the beginning. I am using Access 2007.

 

[riverstar5]

I have attached a copy of the database that I'm currently working on, I have done exactly what you instructed however and I have a better understanding than I did previously. However when I creat the query I get a Enter Parmeter Value box and I enter 01/01/2011 and in return I get back 60 days all all the records. Again, thank you so much for your help.

 

[jzwp22]

The field names listed in the query must match the field names in the table exactly. Your query has the field name as EvaluationDate, but the field name in the Department Employee Info table is: Evaluation Date (with a space).

This is what you currently have:

SELECT [Department Employee Info].[First Name], [Department Employee Info].[Last Name], [Department Employee Info].[Evaluation Date], DateAdd("d",60,[EvaluationDate]) AS NextEvaluationDue, IIf([EvaluationDate]>=DateValue(Now()),[EvaluationDate]-DateValue(Now()),"overdue") AS DaysUntilNextEvaluation
FROM [Department Employee Info];

This is what is should be:

SELECT [Department Employee Info].[First Name], [Department Employee Info].[Last Name], [Department Employee Info].[Evaluation Date], DateAdd("d",60,[Evaluation Date]) AS NextEvaluationDue, IIf([Evaluation Date]>=DateValue(Now()),[Evaluation Date]-DateValue(Now()),"overdue") AS DaysUntilNextEvaluation
FROM [Department Employee Info];

Regarding this expression in your query:

DateAdd("d",60,[Evaluation Date]) AS NextEvaluationDue

This will show the next evaluation date as 60 days from the value in the Evaluation Date for ALL employees which is not what you wanted based on your earlier posts. You need to distinguish new employees from old employees

Also, you can use the date() function in place of the now() function that way you would not need to apply the datevalue() function.

Further, in your expression for DaysUntilNextEvaluation, you are using the Evaluation Date (which could be last year's evaluation), you want to use the NextEvaluationDue in the expression since you want to find out how many days you are away from that date.

I have added a new query to the attached database (qryEvaluationDueAdjustedForNeworOldEmployees) that does things closer to what you were after initially.

BTW, it is generally recommended to not have spaces or special characters (including: (,), $, #, &, /,\, etc.)) in your table and field names. Having them forces you to enclose all names within square brackets [].

 

[riverstar5]

Without a doubt you are the best, I could have never figured that out without your assistance. Thank you soooooooo much!

 

[jzwp22]

You're welcome; I'm just glad I was able to help.

 

[riverstar5]

I was wondering if there was a way if when the due date entered 30 days or less that the number would turn red. Is there a way to do this?

 

[riverstar5]

Re: I want my mommy.....
I was wondering if there was a way if when the due date entered 30 days or less that the number would turn red. Is there a way to do this?

 

[jzwp22]

When using a form, yes you can change colors based on the value. You would use code in the after update event of a control.

 

[riverstar5]

So I could do this using a Form?