Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Presenting data in a grid (2000(SR3))

    I am back to working on the church database. On the quarterly statements we send out, we want to put in a grid that will present previous year's offerings that will look something like this:
    Date 2003 2002 2001
    Q1 $50 $45 $40
    Q2 etc...
    Total $200 $175 $160

    This will get tucked into the header for the statement. I will be saving this data in a separate table for two reasons: 1) on an annual basis, the DonationDetails table has in excess of 10,000 records so with 4 years of data, it will get too bloated for our needs and 2) if the data is saved as hard records rather than calculating it on the fly, processing and printing speed is much quicker. In addition, at year end the data is never supposed to change anyways. Data more than 4 years old will be deleted annually so the table will have a consistent fixed size. In the current year, all totals are calculated on the fly.

    The fields in the historical data table are: Envelope Number, Fiscal Year, Quarter, Amount. The first 3 fields constitute the primary key. The quarterly statements are created based on Envelope number.

    My question: what is the best way to do this? Can I use a series of unbound text boxes to display this info? in which case what would the syntax be for putting the equivalent of a WHERE clause in an unbound textbox? Alternatively, if I have to use a subreport, how do I get this kind of multi year grid? Is this a job for a crosstab query and if so, how do I filter on each envelope number?

    Many thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Presenting data in a grid (2000(SR3))

    Create a query based on your table.
    Select Query | Crosstab Query.
    Set it up like this:
    <table border=1><td>Field:</td><td>Envelope Number</td><td>Fiscal Quarter</td><td>Year</td><td>Amount</td><td>Total:</td><td>Group By</td><td>Group By</td><td>Group By</td><td>Sum</td><td>Crosstab:</td><td>Row Header</td><td>Row Header</td><td>Column Header</td><td>Value</td></table>
    Test that it works OK, then save it. You can now create a make-table query based on the crosstab query, and run it to create a new table.
    Use this table (or the crosstab query, of course) as record source for the report.
    The Envelope Number will be the grouping level. Put the labels for the years (the field names) in the group header.
    Sort on Fiscal Quarter within the group level.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Presenting data in a grid (2000(SR3))

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Thanks for the reply, Hans. It will be a while before I can try it, but it looks straightforward. I will post back if I have any problems.

    BTW, thanks also for you concat function posted in <post#=324705>post 324705</post#>. I used it recently for part of this same database and it worked like a charm. Saved me hours of work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •