Hi,
I've been trying to work out how to create a report based on crosstab
query for which the number of fields is variable. For example in a
situation where you show customer billing by year in the following
format:
Customer 2000 2001 2002
Bill 103 10 205
Frank 12 50
George 200 50
where you might need a new column to appear where 2003 or 1999
billings are in the data.
The good news is that I've come across some really clever code in the
"bible" (Access 2000 Developers Handbook) which can do this. No doubt
this code is readily available in other places too.
Basically the code is placed in the report's open event and it
populates the controls in the report so that they link up with the
field in the cross tab query. (Let me know if I need to expand on
this).
My problem now though is that I want to sort this data by the billings
(eg 2002, then 2001 then 2000).
My thinking here that there must be a relatively easy way to include
something in the code which populated the Grouping and Sorting
property of the form.
Anyone know what the code is? or have a better idea?
Mat