Good afternoon loungers, I am an access noob and trying to figure out the query to return the data I need. My data set contains Fiscal year (FY) as a field. and for some reports I need to look a the last 3 completed fiscal years. I have the following that was inherited with the database will allow me to return just the last completed FY but need assistance in updating this to give me the last three years:
SELECT Actual_Payroll_Data *
FROM Actual_Payroll_Data
INNER JOIN (SELECT MAX(FY)-1 AS MAXFY FROM Actual_Payroll_Data) AS a
ON Actual_Payroll_Data.FY = A.MAXFY;
Thanks for looking,
Greg
returning the last three completed years data
-
- StarLounger
- Posts: 67
- Joined: 22 Feb 2010, 20:15
- Location: Indiana
-
- Administrator
- Posts: 78537
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: returning the last three completed years data
Try this:
SELECT Actual_Payroll_Data.*
FROM Actual_Payroll_Data, (SELECT MAX(FY)-1 AS MAXFY FROM Actual_Payroll_Data) AS A
WHERE Actual_Payroll_Data.FY Between A.MAXFY-2 And A.MAXFY;
SELECT Actual_Payroll_Data.*
FROM Actual_Payroll_Data, (SELECT MAX(FY)-1 AS MAXFY FROM Actual_Payroll_Data) AS A
WHERE Actual_Payroll_Data.FY Between A.MAXFY-2 And A.MAXFY;
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 67
- Joined: 22 Feb 2010, 20:15
- Location: Indiana
Re: returning the last three completed years data
Hans, Worked like a charm. I had tried using the Between line, but I think I actually had it reversed and put the -2 at the end. DOH.
Thanks - Greg
Thanks - Greg