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

    Forcing columns in a crosstab query (2000 SR2)

    Hi folks,

    I have a database which does invoicing and A/R among other things. I want the customer statements to include an aging summary where invoices total into the bins of Current, 31-60, 61-90, and 90+. I had previously requested help and obtained the following solution:

    (This is contained in a crosstab query<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    IIf(Int([DaysOpen])<=30,30,IIf(Int([DaysOpen]) Between 31 And 60,60,IIf(Int([DaysOpen]) Between 61 And 90,90,120)))

    I then used this crosstab query as the basis for another query that changes "30" to "Current", "60" to "31-60", etc., and this second query as the basis for a report.

    My problem is that at the present time, there are no invoices in the system where DaysOpen are less than 120; consequently, the crosstab query only creates one column, "120", and so the second query and report fail because they don't see the other three columns.

    Is there any way to force the columns to appear, whether or not there is data associated? Or is there some other way around this?

    Thanks in advance for your help.

    Regards,
    Charlie T.

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

    Re: Forcing columns in a crosstab query (2000 SR2)

    Open the crosstab query in design view.
    Rightclick on a blank part where the tables are displayed. Select Propreties.
    In the second property, Column Headings enter :
    "30","60","90","120"
    This will force the creation of empty columns
    Francois

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

    Re: Forcing columns in a crosstab query (2000 SR2)

    Muchas gracias, Francois! ( If I knew how, I'd spell merci beaucoups. :-) )
    I'm glad it was such a simple issue to resolve.

    Thanks again.

    Charlie T.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Forcing columns in a crosstab query (2000 SR2)

    >>If I knew how, I'd spell merci beaucoups<<

    Mercy buckups?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing columns in a crosstab query (2000 SR2)

    Murky Buckets??? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

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