Results 1 to 2 of 2
2005-02-23, 04:47 #1
- Join Date
- Feb 2005
- Plymouth, Michigan
- Thanked 0 Times in 0 Posts
Complex Sorting for a Report (2003 SP1)
I am in way over my head here and could certainly use some assistance. I need to create a report in the form shown in the attached document. I have a table of lease data which includes lease end date, leasing division, and monthly lease amouts. I want to create a report which is grouped by month by division and has the monthly lease payment sumed into catagories based upon the lease payment amount (i.e., >$100K, <$100K and >=$75K, etc.). I also want to show the count of the number of lease payments in each total, and calculate the % each catagory is of the total by month. I can do it in Excel but what's the fun in that.
If anyone can provide some assistance, I would be most appreciative
2005-02-23, 10:30 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Complex Sorting for a Report (2003 SP1)
Welcome to Woody's Lounge!
This is fairly complicated. I have attached a demo database that shows how it can be done. Key points:
- A table tblRanges that defines the ranges (categories) into which you want to divide the lease payments.
- Two crosstab queries qrySum and qryCount to compute the sum and count of payments by division and by month in each category.
- To get the sort order of the column headings right, I set the Column Headings property of the crosstab queries. Being lazy, I used a bit of VBA code to generate it.
- A report with two subreports - one for the sum and one for the count.