 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / June 2007How to make a query choose the last three years
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!! 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  SignatureGood Luck BS"D > Hi, > I have a database for employee ranking. And there are 3 tables, one for [quoted text clipped - 3 lines] > Can any body help to do it? > Thanks!! hi, where should i put the sql code? > To get the last three years, try in the DateField condition > [quoted text clipped - 11 lines] > > Can any body help to do it? > > Thanks!! does not work > To get the last three years, try in the DateField condition > [quoted text clipped - 11 lines] > > Can any body help to do it? > > Thanks!! Can you post the SQL you have? SignatureGood Luck BS"D > does not work > [quoted text clipped - 13 lines] > > > Can any body help to do it? > > > Thanks!! >does not work 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] > does not work Jon, What error did you receive and what was the SQL code you used? Sincerely, Chris O. 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 > [quoted text clipped - 5 lines] > > Chris O. 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 > Thank you all for your cooperation > This is what I did [quoted text clipped - 18 lines] > > > > Chris O. hi, still does not work > Hi Jon, > If the Year field contain only the Year and not a full date, try: [quoted text clipped - 39 lines] > > > > > > Chris O. 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] > hi, > still does not work [quoted text clipped - 42 lines] > > > > > > > > Chris O. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.