Vacation by fiscal year Pt 2

  • Thread starter Thread starter 442 Sqn
  • Start date Start date
4

442 Sqn

I have a query that calculates the annual vacation time by
summing the total used in a year by the start date
(StartDate) by year. This was done using the wizard to
generate the query. I was given an initial idea on how to
create a query using the following:

FisYear: Year(DateAdd("m", -3, [SomeDateField]))

But, i've been unable to manipulate the data correctly.
Does this have to be a seperate query or can I modify what
I have and if so, How?

I would like the date to generate the sum total a person
uses during teh year, by the fiscal year.
ie from 01 Apr 'yy' - 31 Mar 'yy'
 
Probably the easiest way to do this is to create a calculated field that
holds the fiscal year value (i.e. 2002) and then use the aggregate method to
group by fiscal year and employee and sum the vacation time taken. To get
the fiscal year, a formula similar to this one can be used:

If the fiscal year 2003 falls between April 2003 and March 2004 then use
this formula:
FiscalYear: Year([VacationDate]) - IIf(Month([VacationDate]) < 4, 1, 0)

Otherwise, if the fiscal year 2003 falls between April 2002 and March 2003
use this formula:
FiscalYear: Year([VacationDate]) + IIf(Month([VacationDate]) > 3, 1, 0)

For more information, go into the VBA IDE (ALT+F11) within Access and look
up the IIf(), Year() and the Month() functions in the Help system. Also
look up "Total records in a query" in the Help system from the Access window
to get information on using aggregate and grouping methods in queries.

Hope this helps,
- Glen
 
Back
Top