Sorting crosstab by fiscal month 1

[BigChinoDon]

Hi all,

I work in a hospital and have a database for deaths. The crosstab query (below) works fine, but I need to sort by fiscal month of the US federal fiscal year. October should be the first month through September, the last month. I've thought about creating a second table listing the months and their sort order (ex: OCT =1, NOV = 2, DEC = 3, etc...) but don't know how to get it to join.

Any thoughts on the best way to do this?

Thanks,
Don


TRANSFORM Count(Deaths.[Patient Name]) AS [CountOfPatient Name]
SELECT Format([Date of death],"mmm") AS [Month of Death]
FROM Deaths
WHERE (((Deaths.[Date of Death])>=#10/1/2009# And (Deaths.[Date of Death])<=#9/30/2010#))
GROUP BY Format([Date of death],"mmm")
PIVOT Deaths.Unit;

 

[SkipVought]

hi,

Fiscal month and calendar month are not necessarily the same. Maybe the federal fiscal year is Oct 1 thru Sep 30, but I think you're after calendar month summarization rather than fiscal month.

You really have TWO issues: 1) summarizing your data by the accounting month, which usually is EXACTLY 4 of 5 weeks, if that's REALLY the case, and 2) sorting the dates in the proper sequence.

For the latter issue...

TRANSFORM Count(Deaths.[Patient Name]) AS [CountOfPatient Name]
SELECT Format([Date of death],"yyyy-mm") AS [Month of Death]
FROM Deaths
WHERE (((Deaths.[Date of Death])>=#10/1/2009# And (Deaths.[Date of Death])<=#9/30/2010#))
GROUP BY Format([Date of death],"yyyy-mm") 
PIVOT Deaths.Unit;

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BigChinoDon]

Skip,

Thanks for the reply. Yeah I guess calendar month summarization is probably the right term. As the query is for FY-2010, I don't really need to see the year, hence my need for a sort by starting with OCT through SEP. You did get me on the right path. I added a "NOT SHOWN" column to the query and added your sort order.

Sometimes the answer is so easy.

Thanks,
Don


TRANSFORM Count(Deaths.[Patient Name]) AS [CountOfPatient Name]
SELECT Format([Date of death],"mmm") AS [Month of Death]
FROM Deaths
WHERE (((Deaths.[Date of Death])>=#10/1/2009# And (Deaths.[Date of Death])<=#9/30/2010#))
GROUP BY Format([Date of death],"yyyy - mm"), Format([Date of death],"mmm")
ORDER BY Format([Date of death],"yyyy - mm")
PIVOT Deaths.Unit;