Grouping by month year 1

[oanion]

Good morning,

I’m trying to create a cross tab report that will have a column heading related to a date field in database. The rows of the crosstab will be a grouping of agency names. For each agency name, for each month/year combination, there is a paid value. My problem is I do not know how to group by the month/year and at the same time capture all the payments between the month/year time frame for a specific agency. The result should look like this
2006-01 2006-02 2006-03
Agency A
Agency B
Agency C
Agency D

How can I extract the month and year from a date field and use as a column heading and at the same time, group the payments that belong to a specific month/year. The date field is called paydate. The format of the field is mm/dd/yyyy

 

[Ladyazh]

How is data stored in your table? When you mentioning month/year what does it mean?

 

[mocgp]

Here is one way to get the year-month format that you are expecting - yyyy-mm

CStr(Year({Date}),0,"")&"-"&
If Month({Date})<=9 then "0"+
CStr(Month({Date}),0,"") else
CStr(Month({Date}),0,"")

Assign this as your other group and summarize your cross-tab accordingly.

(This may not be the only way to do it, but it works for me.)

 

[oanion]

the data in the table is stored as a date in the mm/dd/yyyy format. I only need to group by the month and year. The day of the month is not important because of the grouping. For instance, if I had payments on 08/01/2006, 08/02/2006, 08/09/2006, 09/03/2006, 09/13/2006; the groups I would need are 08/2006 and 09/2006. The payments for specific dates would fall into 1 of the groupings by month and year.

 

[oanion]

Wow!! Thanks Mocgp. It worked perfectly!!!

 

[lbass]

If your date field is a date datatype, then all you need to do is use {table.date} as your crosstab column and while it is highlighted->group options->select "print on change of month".

-LB