Cross tab query-monthwise dates

[Mansoor Ahmad]

Dear All

I have a crosstab query. In it there is one field for Analysis date that has been set to group by months. The query is linked to a report.

Analysis date ranges over 2-3 years. But when I get data of report, it presents the dates as Jan, Feb, Mar etc. and presents all data say for January of all three years under Jan. I want the data to be presented as Jan-01 (for year 2001), Jan-02 (for year 2002) and so on. I have tried to change the date format on crosstab query design grid, but did not work.

Please help me.

Mansoor

 

[KevinM]

You need to group by Month and Year in ONE column.

This can be done by using the format or DatePart function on your date field.

Do this in a select query and then base your crosstab query on this.

 

[Mansoor Ahmad]

Thank you for your reply. I was away for some days that is why it took that long to respond.

I could not workout how you group by month and year in one column but anyway the problem has been solved. I thought I should let you know as well.

When I was changing the date format in already created crosstab query design grid where the date was set as format by month (mmm), nothing was happening but when I created new crosstab query and instead of selecting 'month' for date during wizared, I selected 'date' and later in crosstab design grid I changed format to 'mmm-yy' and it worked. Don't know why but it did the trick.

Thanks again.