Fiscal Year to Now () Totals 1

[Countymnca]

Hello,
I have seen several threads (thread701-1167093) that have me pretty close to fixing my problem, however, something is still not correct.

I need to total the number of training hours per person by the current fiscal year (one query) and for the last 2 fiscal years (2nd query). I used the Where statement from the thread listed above and am getting an error.Below is my code....the working line before just used the Where statement with the normal Between [] And []. I used to use a rolling 12 months and 24 months, but the requirement for this type data is now fiscal year (7/1/yy - Now().

How is this done?
Working Code that gives rolling 12 mths

SELECT [tbl Training].IDNumber, Sum([tbl Training].Hours) AS SumOfHours, [tbl Training].ClassType
FROM [tbl Training]
WHERE ((([tbl Training].ClassDate) Between Date() And Date()-365))
GROUP BY [tbl Training].IDNumber, [tbl Training].ClassType
HAVING ((([tbl Training].ClassType)="S.T.C."))
ORDER BY Sum([tbl Training].Hours);

 

[Golom]

For the Current Fiscal Year

SELECT  T.IDNumber, Sum(T.Hours) AS SumOfHours, T.ClassType
FROM [tbl Training] As T
WHERE T.ClassDate Between 
DateSerial(Year(Date)-
IIF(DateSerial(Year(Date),7,1) < Date,1,0),7,1) And Date() 
  AND T.ClassType='S.T.C.'
GROUP BY T.IDNumber, T.ClassType
ORDER BY Sum(T.Hours);

and for the last two fiscal years (actually for the current one and the previous one)

SELECT  T.IDNumber, Sum(T.Hours) AS SumOfHours, T.ClassType
FROM [tbl Training] As T
WHERE T.ClassDate Between 
DateSerial(Year(Date)-
IIF(DateSerial(Year(Date),7,1) < Date,2,1),7,1) And Date() 
  AND T.ClassType='S.T.C.'
GROUP BY T.IDNumber, T.ClassType
ORDER BY Sum(T.Hours);

This just looks at the start of the fiscal year and decides if it is before or after the current date. If its before then it goes to the July 1st in the previous year (or 2 years previous for the second query.)

 

[Golom]

Oops ... sorry. Got the test wrong. It should be testing if the start of the fiscal year is after the current date (>) rather than before it (<).

SELECT  T.IDNumber, Sum(T.Hours) AS SumOfHours, T.ClassType
FROM [tbl Training] As T
WHERE T.ClassDate Between 
      DateSerial(Year(Date)-IIF(DateSerial(Year(Date),7,1) > Date,1,0),7,1) 
      And Date() 
  AND T.ClassType='S.T.C.'
GROUP BY T.IDNumber, T.ClassType
ORDER BY Sum(T.Hours);

and for the last two fiscal years (actually for the current one and the previous one)

SELECT  T.IDNumber, Sum(T.Hours) AS SumOfHours, T.ClassType
FROM [tbl Training] As T
WHERE T.ClassDate Between 
      DateSerial(Year(Date)-IIF(DateSerial(Year(Date),7,1) > Date,2,1),7,1) 
      And Date() 
  AND T.ClassType='S.T.C.'
GROUP BY T.IDNumber, T.ClassType
ORDER BY Sum(T.Hours);

 

[Countymnca]

Golom,
Thanks for the quick answer. I am getting a Syntax error and am confused about the 2nd line "FROM [tbl Training] As T
". I have tried with your suggestion and without and it isnt working. What is the purpose of that section?

Below is what I have for the current year query

SELECT [tbl Training].IDNumber, Sum([tbl Training].Hours) AS SumOfHours, [tbl Training].ClassType
FROM [tbl Training]
WHERE ((([tbl Training].ClassDate) Between
DateSerial(Year(Date)-IIF(DateSerial(Year(Date),7,1) > Date,1,0),7,1)
And Date()
AND ([tbl Training].Classtype)= "S.T.C."
GROUP BY [tbl Training].IDNumber, [tbl Training].ClassType
ORDER BY Sum([tbl Training].Hours);

 

[PHV]

Simply count your left and right parens ...

 

[Golom]

Just some spurious parens

SELECT [tbl Training].IDNumber, Sum([tbl Training].Hours) AS SumOfHours, [tbl Training].ClassType
FROM [tbl Training]
WHERE [tbl Training].ClassDate Between 
      DateSerial(Year(Date)-IIF(DateSerial(Year(Date),7,1) > Date,1,0),7,1) 
      And Date() 
AND [tbl Training].Classtype= 'S.T.C.'
GROUP BY [tbl Training].IDNumber, [tbl Training].ClassType
ORDER BY Sum([tbl Training].Hours);

The syntax FROM [tbl Training] As T just assigns an Alias to [tbl Training] so that you can refer to it as "T". There are some instances where an alias is required. Here it's just a convenience that allows a bit less typing and (for me at least) makes the statement easier to read.

 

[Countymnca]

Golom,
Thanks for the assistance and explaination. I will try it when I am back at work tomorrow!!!

 

[Countymnca]

Hello again,
I tried your suggestion and receive a prompt named Date for a parameter. When I enter a date it will pull the proper data. I have no experience with the DateSerial command at all, and my books have very little on it. I am planning on using these queries to display training hours on a form, so I need them to run without prompts. Any other suggestions would be greatly appreciated....

 

[Countymnca]

I got it working....

SELECT [tbl Training].IDNumber, Sum([tbl Training].Hours) AS SumOfHours, [tbl Training].ClassType
FROM [tbl Training]
WHERE ((([tbl Training].ClassDate) Between DateSerial(Year(Date())-IIf(DateSerial(Year(Date()),7,1)>Date(),1,0),7,1) And Date()) AND (([tbl Training].ClassType)='S.T.C.'))
GROUP BY [tbl Training].IDNumber, [tbl Training].ClassType
ORDER BY Sum([tbl Training].Hours);