Results 1 to 3 of 3
  1. #1
    Blackcat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    1

    Changing financial years in a query


    Previously the financial year was calendar year and so Jan 15 was set up as Year 2015, Period 1 and so on, so Dec 15, was Year 2015 Period 12. However the financial year has changed to ending on 31 March, instead of 31 December! is there a way I can set my table up that when I ask to attach data to a table, it asks me to assign Apr 16 to Mar 17, that it attaches Year 2016 Period 4 to 12 and Year 2017 Period 1 to 3, just that I find I have to create 2 separate queries! Any ideas?

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I have dealt with this in the past. Because "period" is arbitrary & abstract i.e. March being Period 1... One should have a Period Table that simply consists of the Month (numerical) and Period. 12 rows.

    Then assuming there are real date fields in the actual data: when needed one can isolate the month value and link it to the Period Table in order to have the Period value. The actual implementation depends on the structure of the data and when the Period Value needs to be displayed.

    There is a wrinkle however if one changes the Month/Period - in that looking back at past data things are going to display the current Month/Period: that may either be good or bad depending on the organizational view point on this point. If bad, then one would need a 3rd column with an Effective Date and get involved in a more complex If/then as to which Period value should be used.

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    a calculation to determine which period a date should be applied to, based on a financial year 1st Apr-31st Mar, April being month 1 and the year described as the end year (e.g. 5th May 2016 would result in 201702) is

    ((year(mydate)-(month(mydate)>3))*100)+(month(mydate)+8) mod 12+1

    Note the 3 - denotes March (so a year end of 30th April, you would use 4)
    and the 8 is 12-3-1 (so a year end of 30th April would be 12-4-1=7, Dec would result in -1 which is OK)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with query for financial report
    By iProRyan in forum Queries
    Replies: 12
    Last Post: 02-13-2014, 03:50 AM
  2. Replies: 1
    Last Post: 08-31-2012, 04:41 AM
  3. Financial Year Query Help
    By Kirsti in forum Queries
    Replies: 12
    Last Post: 07-04-2012, 10:19 PM
  4. Financial Query
    By Luke in forum Queries
    Replies: 8
    Last Post: 06-30-2011, 12:33 PM
  5. Replies: 1
    Last Post: 07-12-2010, 12:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums