My table consists of 3 fields,student,date,value. Each time a student pays, its value is recorded along with the date (as a serial number). I now wish to produce a report listing the student names across as headers (there are not more than 10) and then calculate 2 summary values for each month - Sum and count. Ideally the summary values should be next to each other, but if not then below each other. The months should be arranged in order.
I managed to make a crosstab query but have 2 problems.
Firstly one cannot have such a query containing both Sum & Count
Secondly how to arrange the months in correct order since to show them by their names, I must use format(serialDate,"mm/yyyy") which turns them into a string. but as soon as I add the serial numbers into the query its is not grouped per month anymore and I cannot hide the date field.
Maybe I'm supposed to do the calculations in the report? and how?
Maybe I should not use a crosstab query at all?
Thanks for any ideas
I managed to make a crosstab query but have 2 problems.
Firstly one cannot have such a query containing both Sum & Count
Secondly how to arrange the months in correct order since to show them by their names, I must use format(serialDate,"mm/yyyy") which turns them into a string. but as soon as I add the serial numbers into the query its is not grouped per month anymore and I cannot hide the date field.
Maybe I'm supposed to do the calculations in the report? and how?
Maybe I should not use a crosstab query at all?
Thanks for any ideas
