[jakester]

Hello -

here's my problem. I'm trying to put together a crosstab query that groups admit dates to the hospital by month for a particular nursing home. Here's how I have set it up so far:

I have Admit date set as the column heading for the crosstab query. I have nursing home facility set as the row heading and I have admit date set as the value. What I am attempting to do is count the number of admissions to a nursing home for each month.

This is the way the crosstab appears when I run it:

Nursing Home 10/10/08 10/15/08 10/17/08 11/5/08 11/17/08
XYZ Home 1 1
Fountain of Life 1 1 1
Last Days 1 1 1

Can I group the months together so that I see the admit dates rolled up into 1 month? So, I'd like to see:

Nursing Home 10-08 11-08
XYZ Home --------- 2 ------
Fountain of Life----- 2 ------ 1
Last Days --------- 2 ------- 1

I've looked at countless ways in which people have attempted to group dates together in access but no one has attempted a similar problem so I'm stuck.

Can anyone help? Sorry, but I can't get the formatting in the body of this question to cooperate. The counts for each date don't line up accordingly but hopefully you get that there are 2 admits per nursing home in Oct and 1 admit for November for 2 out of the 3.

Thanks.

[ScottGem]

When you use the crosstab wizard (and I just tried this) if you select a date field as the Column headings, it asks for an Interval (of which month is a choice). That should do it.

By the way, I wouldn't count the date field, I would count the PK field.

[jakester]

When you use the crosstab wizard (and I just tried this) if you select a date field as the Column headings, it asks for an Interval (of which month is a choice). That should do it.

By the way, I wouldn't count the date field, I would count the PK field.

Scott -

Ok, I got this to work for grouping by month. But then I encountered another problem which I hadn't contemplated. My records are being grouped together by month, regardless of the year the record takes place in. So I created a Format function to include year in the results. That kept records by month in their correct year buckets.

The problem I have now is that the columns are sorted in alphabetical order. For example:

Apr-08 -- Dec-08 -- Feb-09 -- Jan-09

Ideally, I'd like to keep the columns formatted chronologically. Anyway, to do that?

Here's my SQL code if that helps:

TRANSFORM Count([IDE - 2009].[Admit Date]) AS [CountOfAdmit Date]
SELECT [IDE - 2009].[Service Facility], Count([IDE - 2009].LineOfBusiness) AS [Total Admits]
FROM [IDE - 2009]
GROUP BY [IDE - 2009].[Service Facility]
ORDER BY Format([Admit Date],"mmm-yy")
PIVOT Format([Admit Date],"mmm-yy")
WITH OWNERACCESS OPTION;

Thanks to you or anyone who can help.

[ScottGem]

Were you using a Date field? For the column headings? If you want nt to compare years, you can setup levels of intervals. You can set it to display all the months within a year or all the motnsh for each year side by side.

Ex:
2009
Jan Feb Mar etc

Jan
2008 2009

It just depends on the order you setup.

You do NOT need to format a date in your query and in fact shouldn't because you won't get chrono order.