Problem with reprot based on a Crosstab Query (1 Viewer)

[keving]

I have a problem with a report based on a crosstab query

The crosstab query shows the number of players per team by date as the sample below shows.

Date Total of Last 1 2 3 4 5 …..
2006/05/11 173 11 8 8 9 11
2006/05/18 149 8 7 10 8 11
2006/05/25 181 13 7 11 10 11
2006/06/01 160 11 8 9 10 8
2006/06/08 162 7 11 8 11 10
…

I was asked if I could provide totals for each team over all the dates. I couldn't see a way to add totals within the crosstab query so I created a report based on the crosstab query.

The report works with out a problem until I try to group by year. When grouping by year the order of the dates comes out incorrectly, if I group by month or day the sort order works fine. I typed the following sample to show what I am seeing. I have also attached a screen print of the report output.

Date Team 1 Team 2 Team 3 ......

2006/07/13 10 6 10
2006/05/18 8
2006/05/25 13
2006/06/01
2006/06/08
2006/06/22
2006/05/01
.....

The totals in the group footer are correctbut as yiou see above the dates are not in the correct order.

If anybody has an idea why the sort order gets messed up I would appreciate the help.

Thanks Kevin ….

 

[GohDiamond]

In the query set the "sort by" for the date field to either Ascending or Decending depending on which date you want to show up first. Move the date field to the first column of the query so that it sorts immediatly on that field when run.
If that doesn't solve the whole problem, try this:
Open the report in design view. Right Click on any open space to access the menu. Select Sorting and Grouping. In the left pane select the Date field and in the right pane select the sort order.

Let us know if this helps,

Goh

 

[keving]

Thanks very much for your help. In the Crosstab query, on which the report is based, the DATE is the first element and it is set to sort ascending. In the report the "Sorting and Grouping" is set to DATE Ascending with a Group on Year. I noticed in my original post that my attachment was missing so I have provided it now. Attached please find a zipped Word document that has screen prints of the various Access panes showing the setup of the query and report.

I am begining to think this is some kind of bug. the query and the report work perfectly if I group on Month or group on nothing. If I group on year the sort order gets lost????

Anyway, I really appreciate your help and if you have time maybe you would look at the attached Word document atleast then you can see what I am trying to accomplish.

Thanks Kevin ......

 

[keving]

I solved the problem and it appears to work 100%. I added another "Sorting and Grouping" entry for the field DATE. So now the "Sorting and Grouping" dialog looks kind of like this;

{{= DATE Accending
DATE Accending

The first Date entry groups on Year value, the second does not. Anyway I am happy that the problem seams to be resolved. If anyone can comment on this seemingly weird behaviour I would appreciate hearing.

GohDiamond thanks for your help.

Thanks Kevin .....