How do I setup Fiscal Yr to begin in October in Access

[Deb]

I'm using 2003. I'm trying to set up a query for assessment testing that is
reported quarterly. The FY begins in October instead of Jan. I have the
quarter column set up for the beginning assessment date but it is calculating
from the system's current FY. I have columns for Post Reading Date, Post
Math Date, Post Language Date. I want the quater column to reflect when the
reporting data is due by the FY beginning in October?

Thanks!

 

[Michel Walsh]

Add3 months, or subtract 9 when you want to get the 'fiscal year': :


? Year( DateAdd( "m", 3, #10/10/2008#))
2009


as example, is pushing the 10th of October 2008 into year 2009. (Subtract 9
months, rather than adding 3, if it has to be part of 2008 instead)


? Year( DateAdd( "m", -9, #9/9/2008#))
2007

as example, if a fiscal year of 2007 is between October 2007 to (including)
September 2008.


Vanderghast, Access MVP

 

[KARL DEWEY]

Fiscal_Year: Year(DateAdd("m",3,[YourDateField]))

 

[Bob Barrows [MVP]]

I'm using 2003. I'm trying to set up a query for assessment testing
that is reported quarterly. The FY begins in October instead of Jan.
I have the quarter column set up for the beginning assessment date
but it is calculating from the system's current FY. I have columns
for Post Reading Date, Post Math Date, Post Language Date. I want
the quater column to reflect when the reporting data is due by the FY
beginning in October?

The easiest way is to use a calendar table with columns for
CalendarDate - Date/Time
FiscalYear - Number
FiscalQuarter - Number
FiscalMonth - Number
FiscalWeek - Number

You will need to populate this table annually. If your fiscal weeks
follow a consistent 445445445445 pattern, you can populate it
automatically.

On the other hand, if, as in my company, the accountants fiddle with
these fiscal weeks to better align the fiscal months with calendar
months when they get too far out of sync, then a manual update process
will be needed. It will give the accountants something else to do

Once you have a calendar table, it is a simple matter to join it to your
assessments table via the calendarDate field.

 

[Allen Browne]

To bump the last 3 months of last year into this year, add 3 months to the
date, by typing an expression like this in the Field row in query design:
DateAdd("m", 3, [Post Reading Date])

You can then calculate the Year() of this result, or the DatePart() to get
the quarter.

 

[Bob Barrows [MVP]]

The easiest way is to use a calendar table

This was assuming that your fiscal weeks and months (FM) always begin on
Sunday, which is the case in my company. For example, in this scenario,
FM 1-2009 might begin on 5-Oct this year (or, depending on how the
accountants define it, 28-Sep). In this situation, simply adding 3
months to 1-Oct 2008, which should be in FM 12-2008, would result in the
wrong fiscal year and month (1-2009).

If your fiscal months always begin on the first of the calendar months,
then the solutions suggested by the other responders will work fine.

 

[Deb]

Yes Bob, our weeks run Sunday to Saturday, but come Oct 1st, it's a new FY no
matter what day it falls. I don't have multiple tables. I have one Client
Info tabel and a qazillion queries. The report needs to reflect which
reporting quarter the post test reading, post test math & language was taken.
example: if the client post tested today in reading, he/she would be in the
4th quarter of the FY. If this same client posted in math on Oct. 1st,
he/she would be in 1st quarter of FY 2009. In my Assessment query I have for
the columns headings from the Client Info table:

Last Name, First Name, Numerator, Denominator, Assessment Date, Tested Out
At Assessement, Pre Reading Grade Level, Reading Scale 1 0 to 367, Reading
Scale 2 368 to 460, Reading Scale 3 461 to 517, Reading Scale 4 518 to 566,
Post Reading Date, Post Reading Score, Inc. Reading, Ach. Reading Literacy,
Exit Date, 1 Year Anniversary, 2 Year Anniversary, 3 Year Anniversary, Post
Reading Reporting Quarter: "Qrt" & DatePart("q",[Post Reading Date]). I need
this to reflect the FY beginning in Oct. I have the same set up for the Math
& Language query columns as above in the same query.

 

[John W. Vinson]

This was assuming that your fiscal weeks and months (FM) always begin on
Sunday, which is the case in my company. For example, in this scenario,
FM 1-2009 might begin on 5-Oct this year (or, depending on how the
accountants define it, 28-Sep). In this situation, simply adding 3
months to 1-Oct 2008, which should be in FM 12-2008, would result in the
wrong fiscal year and month (1-2009).

If your fiscal months always begin on the first of the calendar months,
then the solutions suggested by the other responders will work fine.

An excellent example of the need for the "It Depends" response! Businesses
handle these things diffrently, and clearly Bob's company will require a
different solution than the others. Deb should be sure that she's modeling the
company's actual business rules.

 

[Deb]

I wrote that "m", 3, thing and it worked GREAT! Thank you so much.

John, this is a tool so I am aware of the reporting period and the score
outcome to meet success.. nothing more. Thanks for look'n out for me.

Deb