Results 1 to 2 of 2
  1. #1
    New Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

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