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,
