How to make a query choose the last three years

G

Guest

Hi,
I have a database for employee ranking. And there are 3 tables, one for
employee personnel, one for annul performance, and the last one for employee
courses. In the annul performance table I need a query to select only the
last three years of performance and gives the average.
Can any body help to do it?
Thanks!!
 
G

Guest

To get the last three years, try in the DateField condition

Select Avg([performance]) As Avg From [annul performance]
Where DateFieldName Between Date() And DateAdd("yyyy",-3,Date())

The DateAdd will subtruct 3 years of the current date, so it will return all
the records between today date and date - 3 years
 
G

Guest

hi,
where should i put the sql code?

Ofer Cohen said:
To get the last three years, try in the DateField condition

Select Avg([performance]) As Avg From [annul performance]
Where DateFieldName Between Date() And DateAdd("yyyy",-3,Date())

The DateAdd will subtruct 3 years of the current date, so it will return all
the records between today date and date - 3 years


--
Good Luck
BS"D


Jon said:
Hi,
I have a database for employee ranking. And there are 3 tables, one for
employee personnel, one for annul performance, and the last one for employee
courses. In the annul performance table I need a query to select only the
last three years of performance and gives the average.
Can any body help to do it?
Thanks!!
Click to expand...
Click to expand...
 
G

Guest

does not work

Ofer Cohen said:
To get the last three years, try in the DateField condition

Select Avg([performance]) As Avg From [annul performance]
Where DateFieldName Between Date() And DateAdd("yyyy",-3,Date())

The DateAdd will subtruct 3 years of the current date, so it will return all
the records between today date and date - 3 years


--
Good Luck
BS"D


Jon said:
Hi,
I have a database for employee ranking. And there are 3 tables, one for
employee personnel, one for annul performance, and the last one for employee
courses. In the annul performance table I need a query to select only the
last three years of performance and gives the average.
Can any body help to do it?
Thanks!!
Click to expand...
Click to expand...
 
G

Guest

Can you post the SQL you have?

--
Good Luck
BS"D


Jon said:
does not work

Ofer Cohen said:
To get the last three years, try in the DateField condition

Select Avg([performance]) As Avg From [annul performance]
Where DateFieldName Between Date() And DateAdd("yyyy",-3,Date())

The DateAdd will subtruct 3 years of the current date, so it will return all
the records between today date and date - 3 years


--
Good Luck
BS"D


Jon said:
Hi,
I have a database for employee ranking. And there are 3 tables, one for
employee personnel, one for annul performance, and the last one for employee
courses. In the annul performance table I need a query to select only the
last three years of performance and gives the average.
Can any body help to do it?
Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

does not work
Click to expand...

Then fix the error.


You haven't told us what you did, or in what way it didn't work.

Ofer is really good at queries, but he cannot see your screen and you have
chosen not to post any information that would help him reply.

John W. Vinson [MVP]
 
G

Guest

Thank you all for your cooperation
This is what I did
But it did not work
This is my sql after rplace Date() with 2006 after that it worked

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE ((([P&P_tbl].Year) Between 2006 And DateAdd("yyyy",-3,Date())))
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark];
 
O

Ofer Cohen

Hi Jon,
If the Year field contain only the Year and not a full date, try:

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date()) And Year(Date())-3
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]

Note: It's not recomended using build in functions in Access as fields names
(Year - that will return the year from the date).
It will be OK as long that you place it in square brackets, but to avoid
errors and to have to remember puting square brackets, it's better changing
the name.

------
Good Luck
BS"D

Jon said:
Thank you all for your cooperation
This is what I did
But it did not work
This is my sql after rplace Date() with 2006 after that it worked

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE ((([P&P_tbl].Year) Between 2006 And DateAdd("yyyy",-3,Date())))
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark];


Chris2 said:
Jon,

What error did you receive and what was the SQL code you used?


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
G

Guest

hi,
still does not work

Ofer Cohen said:
Hi Jon,
If the Year field contain only the Year and not a full date, try:

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date()) And Year(Date())-3
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]

Note: It's not recomended using build in functions in Access as fields names
(Year - that will return the year from the date).
It will be OK as long that you place it in square brackets, but to avoid
errors and to have to remember puting square brackets, it's better changing
the name.

------
Good Luck
BS"D

Jon said:
Thank you all for your cooperation
This is what I did
But it did not work
This is my sql after rplace Date() with 2006 after that it worked

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE ((([P&P_tbl].Year) Between 2006 And DateAdd("yyyy",-3,Date())))
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark];


Chris2 said:
does not work


Jon,

What error did you receive and what was the SQL code you used?


Sincerely,

Chris O.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

My mistake, the order in the between should have the small value first

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date())-3 And Year(Date())
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]


Jon said:
hi,
still does not work

Ofer Cohen said:
Hi Jon,
If the Year field contain only the Year and not a full date, try:

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date()) And Year(Date())-3
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]

Note: It's not recomended using build in functions in Access as fields names
(Year - that will return the year from the date).
It will be OK as long that you place it in square brackets, but to avoid
errors and to have to remember puting square brackets, it's better changing
the name.

------
Good Luck
BS"D

Jon said:
Thank you all for your cooperation
This is what I did
But it did not work
This is my sql after rplace Date() with 2006 after that it worked

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE ((([P&P_tbl].Year) Between 2006 And DateAdd("yyyy",-3,Date())))
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark];


:


does not work


Jon,

What error did you receive and what was the SQL code you used?


Sincerely,

Chris O.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to make a query to select the last 3 years 8
Unmatched query between three tables 2
Query not updatable - advise on how to change it 4
The Last of Us - Part I (PC) 6
Ranking results of a Query 3
Multiple Query Report- Really Need HELP! 2
Extract data from three tables 0
New user question - timesheet query 1

Top