Microsoft Access - summing total by fiscal month

I need to create a query/report that will total units produced in a fiscal month by catagory.  For example,  For the current fiscal month 200 units were produced of A , 400 units of B were produced, etc.   Is there an expression for fiscal month?

Fiscal month is typically defined by your accounting department.  Quite frequently, it is the same as calendar month, but not necessarily.  I've seen fiscal periods that end a day or two before the calendar month, and the first month of the fiscal year in May or June.

That said, either way it is possible.

If the fiscal month is the same as the calendar month, you can use a group by query with something like this:

SELECT Year(FiscalDate) & "_" & Month(FiscalDate) as FiscalPeriod, UnitDesc, Sum(UnitsProduced) as SumOfUnits
From tblProduction
Group By Year(FiscalDate) & "_" & Month(FiscalDate), UnitDesc

where FiscalDate is the production date of the units.  If your fiscal dates don't fall on the month boundaries you probably need to create a table defining the fiscal months and join to it.  If the fiscal months don't line up with calendar months, you can do the same with an independent table, or do a calculation to derive the fiscal month.

Does this help?

If your Fiscal month is different from the standard month, you will need to create a function that you pass in some date and it returns the Fiscal Year and Month.  You need year also or you won't be able to do any annual reports.  If the rules are simple, you can code them.  If they are complex, you can use a table that you update every year where you create a record for each Fiscal year/month and include the calendar start date and calendar end date.  For example:
Year month startdate enddate
2012 01 01/03/2012 02/02/2012
2012 02 02/03/2012 03/02/2012
....
2012 12 12/03/2012 01/02/2013

Something like this schema is actually as simple as adding 2 days to the given date to determine the fiscal date where you can extract the year and month.  But, using addition or subtraction rather than table lookup, you will have to account for leap year.

hi refer to the below link,

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

Hoipe it helps you.

Thanks to all of you.  Your suggestions are great and I will work on them and let you know how it turns out.  I forgot to mention that we work on the 4 4 5 fiscal month.  This year (fortunatly ) Jan 1st is the first day of the month.  You wouldn't have an easy one for that would you?????

The "4 4 5 fiscal month" won't mean anything to anyone except an accountant and I doubt you'll find many of those answering technical questions here so I you want help with a calculation, you'll need to spell out the rules in detail.

I will do my best to explain. I will use this year as an example.
January  and February are 4 week months
March is a 5 week month
This pattern continues through out the rest of the year.

For 2012 the 1st day of the 1week of the year is January 1st. 1 fiscal week is Sunday - Saturday 

So the fiscal Calandary look like this for the first three months
January
wk1 Jan 1-7
wk2 Jan 8-14
wk3 Jan 15 - 21
wk4 Jan22-28

 February
wk5 Jan 29 - Feb 4
WK6 Feb 5 - Feb 11
wk7 Feb 12 - Feb18
wk8 Feb 19 - Feb 25

March
wk 9 Feb 26 - Mar 3
Wk 10 Mar 4 - Mar 10
wk11 Mar 11 - Mar 17
wk12 Mar 18 - Mar 24
wk 13 Mar 25 - Mar 30

etc

I know that creating a table with the dates in it for the fiscal month and using that to guide my querier by fiscal month works, I was hoping that there was a formula already created for taking a date in a production date to determine its fiscal month.

Wikipedia defines 4-4-5 in this article - http://en.wikipedia.org/wiki/4-4-5_Calendar
A search did not come with any suggested calculations for your situation.  Take a look at the Format() function.  It has several options for determining the first week of the calendar year.  If it has an option that works for you, you can use Format() to determine the week number of any date as well as the quarter.

Thanks

I will check it out.