Report based on crosstab query... How?

[Hellgofi]

I am trying to solve this problem over 4 hours but still cant find a way. Please help me!

There are 2 tables,tblStudent and tblPayment and these are the field names.
tblStudent:id, namesurname, groupno
tblPayment: id, amountpayed, paymentdate, monthpayed and payed

Montpayed is the name of the month that payment belongs to (Jan., Feb, etc)

Payed is a Yes/no field that shows the payment is done without a problem.
In tblPayment, there can be 0 or 12 (at most) records for each student.

Here is what I'd like to see in the report:

Consider an Excel worksheet, first row contains JAN. FEB.........DEC. and
first column contains the student names. And in the cross-section of each row and column amountoayed is shown. Also, I'd like to group records based on their groupno

I tried a crosstab query and partially done what I want however I couldnt create a report based on that a query.

How can I solve this?

regards

 

[Lucas]

I guess your problem is that the column names in your crosstab query change depending on what data is returned.

Dynamic crosstab queries are possible, allthough I've never tried them, have a look at this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q328320

Alternatively, you could write some code have your crosstab query exported to Excel automatically.

Hope this helps.

 

[Hellgofi]

thanks for the link Lucas but I still couldnt fix the problem. I dont konw what the reason is. Crosstab query shows the results that I want but the report based on that query still doesnt work.