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.

 

[Pat Hartman]

You can use the year part as one of the row headings. This will give you a separate row for each year. If you want all months and years to be columns (you'll run into trouble this way with too many columns. You also won't be able to make a report of this crosstab), you need to use Format(yourdate, "yyyymm") to get the data into the correct order. Month/year order will not work either as text or numbers. You'll end up with
01/2001 01/2002 01/2003 02/2001 02/2002 02/2003 etc.

 

[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.