Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping for an Aging Report (Access 2K)

    Hi folks,
    I wouldn't be surprised if this were a simple matter that I'm just overlooking, but I've tried everything I can think of and haven't yet been successful.
    I want to create statements, with outstanding invoices summed by date (0-30 days old, 31-60, 61-90, over 90). I've created the query with the DaysOpen calculated field, but when I try to create a crosstab query with the info, I get each individual elapsed time as its own column heading rather than the grouping I want. I found an example to group the days by month, but that isn't much help to me.

    Can anyone lend a hand?

    Thanks much.
    CharlieT

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping for an Aging Report (Access 2K)

    Add a column to your query with the following expression
    iif(Int([DaysOpen]/30)>3,3,Int([DaysOpen]))
    and group on that column.
    Maybe you will have to add 1 to DaysOpen to put the 30,60 and 90 in the right category.(I haven't test it)
    iif(Int(([DaysOpen]+1)/30)>3,3,Int(([DaysOpen]+1)/30))
    Francois

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping for an Aging Report (Access 2K)

    Francois,
    Thanks for your reply. I started out using your idea, and then refined it to the following:

    IIf(Int([DaysOpen])<30,30,IIf(Int([DaysOpen]) Between 31 And 60,60,IIf(Int([DaysOpen]) Between 61 And 90,90,120)))

    It was a big challenge to get the syntax right, but it worked!.

    Thanks again for your help.

    Charlie T.

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

    Re: Grouping for an Aging Report (Access 2K)

    If DaysOpen is always positive, you can group on the expression

    Partition([DaysOpen],1,90,30)

    But if DaysOpen = 0 occurs, it will be put in a separate category. The Partition function can't handle negative numbers, but I suppose DaysOpen is non negative.

Posting Permissions

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