CrossTab Query (Fiscal Year)

[megmem]

Is there a way in the Crosstab query to select a fiscal year period. All I see is year, months, ect. When I choose year it does not Format the right dates. For example,
The Fiscal year period is November-October, and i want to compare 3 years. And I want the column headings to be:

November-October November-October November-October
1999 2000 2001

All the information is in one table. Any help will be appreciated. Thanks again

Mark

 

[blom0344]

Hello Mark,

What do you mean with "the crosstab query"? Are you expecting Access to have something standard for shifted (fiscal) years?
Please give more details and example of the table you are working on, in order to get helpfull feedback. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[raskew]

Mark,

If I understand the problem correctly, it's doable.

Using Northwind's Orders and OrderDetails tables to demonstrate:

Given the Orders table's limited date-range (4 Aug 94 to 5 Jun 96), I've
selected 1 Jun XX to 31 May XX as the desired fiscal year. However,
it will work equally well with other From-To date ranges.

The main problem is to place each OrderDate within a specific Fiscal
Year (FY). Do this with:

MyFY: (Int(DateDiff("m",[Enter start date],[OrderDate])\12)+1)

If you're not familiar with use of the backslash (\) in integer
math, look it up in the help file.

So, here's the first query, which will enumerate each item in each order,
returning the gross cost (leaving it up to you to add discounts, etc..)
Copy this to a new query, run it (specifying 06/01/94 when promoted for
the Start Date), and save as Query19.

PARAMETERS [Enter start date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, (Int(DateDiff("m",[Enter start date],[OrderDate])\12)+1) AS MyFY, Orders.CustomerID, Sum([UnitPrice]*[Quantity]) AS Expr1
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderID, Orders.OrderDate, (Int(DateDiff("m",[Enter start date],[OrderDate])\12)+1), Orders.CustomerID, Orders.OrderDate
HAVING (((Orders.OrderDate)>=[Enter start date]))
ORDER BY Orders.OrderDate;

Now you have the basis for the crosstab. Again, copy/paste this code, saving it as Query19_crosstab.

TRANSFORM Sum(Query19.Expr1) AS [The Value]
SELECT Query19.CustomerID, Sum(Query19.Expr1) AS [Total Of Expr1]
FROM Query19
GROUP BY Query19.CustomerID
PIVOT Query19.MyFY;

Hopefully this will bring you to close to what you're after.

Bob