Help on crosstab query

  • Thread starter Thread starter Metcare
  • Start date Start date
M

Metcare

Hi ,
I just started using crosstab query. I am trying to create a crosstab query
for a 2 year worth of data. I want to total my monthly hospital admissions by
hospital facility based on admission date. My row heading would be the
facility name and my column heading would be the months. I was able to create
a crosstab query 2009( Jan - Apr) , and another crosstab query for 2008
months(Jan - Dec) . Is there a way to create a single query to do both 2008
and 2009 .

Also, I need to create a separate query for our external auditor for a
different accounting month cutoff, it runs from 21st to the 20th. How do I do
that?
Greatly appreciate your help. Thanks in advance.
 
Use the PIVOT statement below substituting your field name --
PIVOT Format([YourDateField],"mmm yyyy") IN ("Jan 2008", "Feb 2008", "Mar
2008", "Apr 2008", "May 2008", "Jun 2008", "Jul 2008", "Aug 2008", "Sep
2008", "Oct 2008", "Nov 2008", "Dec 2008", "Jan 2009", "Feb 2009", "Mar
2009", "Apr 2009", "May 2009", "Jun 2009", "Jul 2009", "Aug 2009", "Sep
2009", "Oct 2009", "Nov 2009", "Dec 2009");
 
Regarding your second point the following function should return the
accounting month for any date in the format Nov 2009 - Dec 2009', 'Dec 2009 -
Jan 2010' etc.

Public Function AcctMonth(dtmDate As Date, intStartDay As Integer) As String

Dim intDay As Integer

intDay = Day(dtmDate)

If intDay < intStartDay Then
' accounting month begins month prior to current month
AcctMonth = Format(DateAdd("m", -1, dtmDate), "mmm yyyy") & _
" - " & Format(dtmDate, "mmm yyyy")
Else
' accounting month begins current month
AcctMonth = Format(dtmDate, "mmm yyyy") & _
" - " & Format(DateAdd("m", 1, dtmDate), "mmm yyyy")
End If

End Function

In your query you'd call it with:

AcctMonth([AdmissionDate], 21)

as the column heading and use:

PIVOT AcctMonth([AdmissionDate], 21) IN ("Jan 2009 – Feb 2009", "Feb 2009 –
Mar 2009", etc.

You might want to amend the function to return the accounting month in a
different format, but you'll then need to reflect this format in the query of
course.

Ken Sheridan
Stafford, England
 
Back
Top