p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Updating reports based on Crosstab queries...?


Message #1 by "Ben Clark" <clarkbm@p...> on Tue, 25 Jun 2002 16:31:29
Okay, here's the deal.  I am building an application that uses a crosstab 
query to summarize data by month, with the month as the column head.  The 
query is used as the data source for a two-part trend report which 
contains the summarized data in tabular form and bar chart form.  

The tabular report looks basically like this:

---------------------------------------------
         Apr-2002     May-2002     Jun-2002 |
Area 1      xx           xx           xx    |
Area 2      xx           xx           xx    |
.           .            .            .     |
.           .            .            .     |

My problem arises when new data is added to the database for subsequent 
months.  The bar chart is updated to reflect the new month's data, as is 
the underlying Crosstab query.  However, the tabular section of the report 
is apparently set in digital stone during the report creation process and 
is not updated with the new data.  I have tried embedding the crosstab 
query directly into the report, but this seriously compromised the 
formatting (i.e. it looks terrible).

How do I get the tabular section of the trend report to update with new 
columns as data builds up in the database?  Right now there are only 3 
months worth of data, and the report seems fixed at displaying only those 
3 months.

I need the report to update at runtime when July, August, etc data is 
entered.  Will this be possible, or am I on the wrong path here?

Ben
Message #2 by joe.dunn@c... on Wed, 26 Jun 2002 09:06:05 +0000
To get around the problem of a tabular report based on a crosstab query
getting out of date, I abandoned the crosstab query and built a small
summary table, called (say) OUTPUT_SUMMARY (to hold the results) and a one
row table, called (say) MONTHS_TO_PRINT (with enough columns to hold the
months that I could fit on the report - one of the issues that will arise
is that you may eventually end up with too many months to be fitted on a
page).

The whole process goes like this:

   delete all from the OUTPUT_SUMMARY table
   set to blank the columns in the MONTHS_TO_PRINT table
   scan the source data table and build the MONTHS_TO_PRINT table - this
   gives us the headers for the report. To support 12 months, call the
   columns MONTH1 through to MONTH12.
   scan the source data table and collate the totals for each month
   load the report and format the headers (effectively pre-format the
   headers)
   print the report

Now all this sounds horrific and it needs careful setting up but the
technique (once mastered) is very powerful and can be adapted to handle
complex problems....

....for example, I output a trading report which:

   can be 1,2 or 3 landscape pages wide with 2 columns for each trading
   month AND a set of 2 total columns
   supports the total tonnage and average price in the 2 columns in each
   set
   allows the user to specify the starting trading month from a combo box
   of available months
   works out which months it can fit on a page and prints 'the rest' in the
   column immediately before the totals if (and only if) there are too many
   months to fit on the 3 pages width
   and does it all automatically

I soon discarded the crosstab solution as inflexible!

If this sounds that it may be of use, I can provide further details if you
e-mail me directly. It is several queries and quite a bit of code but if
you give me your source data (a sample of dummy data would be fine) in a
small database, I can adapt my (complex) solution to your requirements.

Joe Dunn

*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************
Message #3 by "Amy Wyatt" <amyw@c...> on Wed, 26 Jun 2002 12:57:16
You would need to rebind your fields in the report to the new names of 
fields (the months) in the crosstab query. This can be done fairly simply 
with a loop in the on open event of the report. This, of course, is 
assuming the number of months displayed is fixed. If you need help with 
the code, let me know and I will send it to you.

Amy


> Okay, here's the deal.  I am building an application that uses a 
crosstab 
q> uery to summarize data by month, with the month as the column head.  
The 
q> uery is used as the data source for a two-part trend report which 
c> ontains the summarized data in tabular form and bar chart form.  

> The tabular report looks basically like this:

> ---------------------------------------------
 >         Apr-2002     May-2002     Jun-2002 |
A> rea 1      xx           xx           xx    |
A> rea 2      xx           xx           xx    |
.>            .            .            .     |
.>            .            .            .     |

> My problem arises when new data is added to the database for subsequent 
m> onths.  The bar chart is updated to reflect the new month's data, as is 
t> he underlying Crosstab query.  However, the tabular section of the 
report 
i> s apparently set in digital stone during the report creation process 
and 
i> s not updated with the new data.  I have tried embedding the crosstab 
q> uery directly into the report, but this seriously compromised the 
f> ormatting (i.e. it looks terrible).

> How do I get the tabular section of the trend report to update with new 
c> olumns as data builds up in the database?  Right now there are only 3 
m> onths worth of data, and the report seems fixed at displaying only 
those 
3>  months.

> I need the report to update at runtime when July, August, etc data is 
e> ntered.  Will this be possible, or am I on the wrong path here?

> Ben
Message #4 by "Ben Clark" <clarkbm@p...> on Fri, 28 Jun 2002 12:42:12
Thanks, Amy.  The number of months to be displayed will be fixed, but most 
will have empty data for several months.  I would be interested in seeing 
your technique in action, so if you could send me a snippet of code, 
either here or to clarkbm@p..., I would appreciate it.

Ben

> You would need to rebind your fields in the report to the new names of 
f> ields (the months) in the crosstab query. This can be done fairly 
simply 
w> ith a loop in the on open event of the report. This, of course, is 
a> ssuming the number of months displayed is fixed. If you need help with 
t> he code, let me know and I will send it to you.

> Amy
Message #5 by "Amy Wyatt" <amyw@c...> on Fri, 28 Jun 2002 13:52:27
This is a sample of the code that I would use to do this:

Private Sub Report_Open(Cancel As Integer)
    Dim intCount As Integer
    Dim rstList As ADODB.Recordset
    Dim strLable As String
    Dim strText As String
    Dim strColumnName As String
    
    Set rstList = New ADODB.Recordset
    rstList.Open Me.RecordSource, CurrentProject.Connection, _
                           adOpenStatic, adLockReadOnly, adCmdTable
    
    For intCount = 1 To 3
        strLable = "lbl" & intCount
        strText = "txt" & intCount
        strColumnName = rstList.Fields(intCount + 1).Name
        Me(strLable).Caption = strColumnName
        Me(strText).ControlSource = strColumnName
    Next intCount
    
    rstList.Close
    Set rstList = Nothing
End Sub

I name the lables something that can me incremented (ie. lbl1, lbl2, lbl3) 
and each label would have a corresponding textbox for the data (ie. txt1, 
txt2, txt3)... you get the idea. You have to make sure you skip the number 
of row values (in my case it is 2) which I do in the 
line 'strColumnName=rstList.Fields(intcount+1).Name (remember that the 
fields collection starts with and index of 0, that is why I am using +1 
instead of +2). This will not only bind the correct fields to the report 
but it will label them also.

Hope this helps,

Amy


> Thanks, Amy.  The number of months to be displayed will be fixed, but 
most 
w> ill have empty data for several months.  I would be interested in 
seeing 
y> our technique in action, so if you could send me a snippet of code, 
e> ither here or to clarkbm@p..., I would appreciate it.

> Ben

> > You would need to rebind your fields in the report to the new names of 
f> > ields (the months) in the crosstab query. This can be done fairly 
s> imply 
w> > ith a loop in the on open event of the report. This, of course, is 
a> > ssuming the number of months displayed is fixed. If you need help 
with 
t> > he code, let me know and I will send it to you.

> > Amy

  Return to Index