Calculated Field Based on Accounting Year

[paulS30berks]

Within a query i am trying to run historical pension values based on 3 fields from a table:

Value
Year
Period

Period: Is aligned with the accounting year April to March rather than Jan to Dec. So I cannot define Year as 2005 and period 1 to 12.

I am looking to define year as 2005/2006 and then calculated the sum of period 1 to 12.

Has anyone experienced this before and found a solution?

Thanks

 

[RuralGuy]

If you add 9 months to each date you would have a fiscal year with periods 1 to 12.

 

[raskew]

Hi -

Here's a sample query, based on Northwind's Orders table, that might provide food for thought.

To use, in a db with a link with the NW Orders table, copy/paste to a new query. Run the query and, when prompted for [enter FYStart], respond with 4/1/95. The return will be all orders between 4/1/95 and 3/31/96, along with the specific period (1 - 12).

HTH - Bob

SELECT
    Orders.OrderID
  , Orders.OrderDate
  , DateDiff("m",[enter FYStart],[OrderDate])+1 AS FYPer
FROM
   Orders
WHERE
   (((Orders.OrderDate)>=[enter FYStart] 
AND
   (Orders.OrderDate)<=DateAdd("yyyy",1,[enter FYStart])-1));