Crosstab query reports (unknown fields in reports) What about sorting?

[Mat N]

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

[Allen Browne]

You can assign the ControlSource of the GroupLevel in Report_Open.

Example at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mat N" <Ma******@hotmail.com> wrote in message
news:d2*************************@posting.google.co m...

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

[Mat N]

Allen,

Just what I needed. Thanks.

Just to complete the thread here's the bit of code the solved my
problem:

'Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
Me.Controls("lblHeader" & i).Caption = strName
Me.Controls("txtData" & i).ControlSource = strName
Me.Controls("txtSum" & i).ControlSource = _
"=Sum([" & strName & "])"
If i > 1 Then
Me.GroupLevel(i - 1).ControlSource = strName
Me.GroupLevel(i - 1).SortOrder = True
End If
Next i

Notice that I included "SortOrder" which when true means descending
and also the if statement avoids sorting by the first field.

Mat N

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@per-qv1-newsreader-01.iinet.net.au>...

You can assign the ControlSource of the GroupLevel in Report_Open.

Example at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mat N" <Ma******@hotmail.com> wrote in message
news:d2*************************@posting.google.co m...

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

[Mat N]

Actually the code I used didn't appear to work properly but I've
worked out the problem.

Basically the code would work except that Grouplevels can only be
created in design view.

The work around is to create dummy grouplevel in the design view which
you edit later using the same code. I designed my report so that it
can show up 7 columns of numbers and so I've simply created the
Grouplevel using the following expressions:

=2
=3
....
=6
=7

It shouldn't matter what you use so long as it is an expression and
you have enough to cover the maximum number of group levels you might
need.

Works a treat now.

Mat N

Ma******@hotmail.com (Mat N) wrote in message news:<d2**************************@posting.google. com>...

Allen,

Just what I needed. Thanks.

Just to complete the thread here's the bit of code the solved my
problem:

'Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
Me.Controls("lblHeader" & i).Caption = strName
Me.Controls("txtData" & i).ControlSource = strName
Me.Controls("txtSum" & i).ControlSource = _
"=Sum([" & strName & "])"
If i > 1 Then
Me.GroupLevel(i - 1).ControlSource = strName
Me.GroupLevel(i - 1).SortOrder = True
End If
Next i

Notice that I included "SortOrder" which when true means descending
and also the if statement avoids sorting by the first field.

Mat N

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@per-qv1-newsreader-01.iinet.net.au>...

You can assign the ControlSource of the GroupLevel in Report_Open.

Example at:
http://allenbrowne.com/ser-33.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mat N" <Ma******@hotmail.com> wrote in message
news:d2*************************@posting.google.co m...

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