I have a report that shows summary information received monthy by locations 
from policy holders.  I would like the information to start with the month of 
the effective date for the policy.  For example,  Policy 1 has an effective 
date on 5/14/07, I would like the information to group like:
                  May 07      June     July ........ April 08
Location1     500           500      500            500
location2                      700      700            700
Location3                     800       800           800

Any help will be appreciated.

dawnecia wrote:
> I have a report that shows summary information received monthy by locations 
> from policy holders.  I would like the information to start with the month of 
> the effective date for the policy.  For example,  Policy 1 has an effective 
> date on 5/14/07, I would like the information to group like:
>                   May 07      June     July ........ April 08
> Location1     500           500      500            500
> location2                      700      700            700
> Location3                     800       800           800
> 
> Any help will be appreciated.
> 
> 

To sort the column names you have to format the PIVOT clause - something 
like this:

PIVOT Format(<date column name>, "yyyy/mm")

This will produce column names like 2007/05 for May, 2007.  It's best to 
put the year first and then the month, this will sort the dates in 
chronological order.

To sort by month name you'd have to actually name the months like this:

PIVOT Format(<date column name>, "mmm") IN ("Jan", "Feb", "Mar", etc)

This precludes inserting the year, 'cuz you'd have to explicitly include 
the year number in the IN () clause.  Because the year changes over time 
you'd have to change the query when the year changed.  That's why it is 
better to use the "yyyy/mm" format.

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **