how to make a query to select the last 3 years

[Guest]

Hi,
I have two tables, one for % values and the other one for employee evaluation
In the % value table, there are 3 fields:
Percent ID>>Primary Key
Percent Value>> points start form 5 to 10
Percent>> Percentage start form 50 % to 100%
In the employee evaluation there are 5 fields
Emp ID
P&P ID>> annual Performance which is 6 types
P&P Name
Percent ID
Year>> start form 2004
P&P Name
I made a form for entering the data accordingly
What I want to do is making a query to select the last 3 years performance
evaluation.
How can I do that plzz???
Thanks!!

 

[Jeff Boyce]

"The last three years" could mean all dates falling after a date three years
before today. To do that in a query, you could use the DateAdd() function
in the selection criterion "cell" beneath your date field.

Open a new query, add the table with the date field, add the date field (and
any others you wished to see). In the criterion 'cell' under the date
field, add something like:

DateAdd("y",3,Date())

"The last three years" could also mean any dates in 2004, 2005, 2006.

Which do you mean?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Hi,
Thank you for your responding. I meant by "the last three years" that the
query selects the last there years enters. In the future, if I enter 2007,
2008, 2009 the query select the last three years regardless 2006, 2005, 2004.
(Select the latest 3 years)
I hope it is clear now

 

[Jeff Boyce]

I have no idea where you are entering 2007, 2008, 2009. Are you putting
those all in a single field?

Access works great if you have a single value in a field, but you'll have to
do a lot of extra programming if you are putting multiple values in the same
field (e.g., "2007, 2008, 2009").

Please provide an example of how you are storing your data...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Hi,
No, it is not together. The employee record will be duplicated; each
employee has more than one field. Each year , there is 5 records for earch
employee. E.g. emp # 12 will be evaluated on 5 tasks. In 2007 , emp 12 has 5
records with the same year.

 

[Jeff Boyce]

I'm sorry, but I'm not visualizing your data/table.

Could you post an example of the data, along with a table structure?
Something like:

tblPerson
PersonID
FirstName
LastName
DateOfBirth

17 John Doe 6/1/1987
18 Jane Smith 1/20/1982
...


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Hi,
Please see the table below:
Emp ID P&P ID Percent value year
1234 1 6 2004
1234 1 5 2005
1234 1 7 2006
1234 1 8 2007
1234 2 5 2004
2345 1 7 2004
2345 1 8 2005
2345 2 9 2004
..
..
..

Each employee has 6 P&P ID and each P&P has more than 3 years of evaluation
What I want is to select the latest percent value based on Year. If we are
in 2007, the query will take the 2005, 2006, 2007 and so on.
Thank you so much

 

[Guest]

Hi,
Please see the table below:
Emp ID P&P ID Percent value year
1234 1 6 2004
1234 1 5 2005
1234 1 7 2006
1234 1 8 2007
1234 2 5 2004
2345 1 7 2004
2345 1 8 2005
2345 2 9 2004
..
..
..

Each employee has 6 P&P ID and each P&P has more than 3 years of evaluation
What I want is to select the latest percent value based on Year. If we are
in 2007, the query will take the 2005, 2006, 2007 and so on.
Thank you so much

 

[Jeff Boyce]

If I'm understanding your data and what you want to find, it seems like you
could use a Totals query, grouping by EmployeeID and averaging the percent
value, where PercentValueYear is between Year(Date())-2 and Year(Date()).

Create a new query. Add the table. Add the three fields mentioned above.
Click on the Totals button (the Greek sigma character).

Leave the GroupBy for EmployeeID, change it to Average for the percent value
field, change it to Where for the PercentValueYear and in the Selection
Criterion "cell" under that field, put Between Year(Date())-3 And
Year(Date()). Run it.

Now, for the main problem. If your table holds percentages, there's only
one situation in which it makes good (mathematical) sense to average those
percentages -- that's if there are exactly the same number of measurements
each year on which percent is calculated. Otherwise, you can do the math,
but the number means about as much as calculating the average gender, where
Male = 1 and Female = 2.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/