Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab query - detail and summary together (2000

    Greetings from outside the Access world (I don't venture here too often)!!!

    I have a table sitting on an Oracle server, that I access via ODBC connections. I've "linked" the table named TAXINSTALLMENT to my Access database. In this table, are the following columns:
    <UL><LI>installmentrsn
    <LI>controlbatchrsn
    <LI>folderrsn
    <LI>billrsn
    <LI>yearforbilling
    <LI>installmentnumber
    <LI>batchsubcode
    <LI>duedate
    <LI>sumduetax (values are positive)
    <LI>sumduepenalty (values are positive)
    <LI>sumpaidtax (values are negative)
    <LI>sumpaidpenalty (values are negative)[/list]I've created a query (Instalment Balances) that brings in data that represents any folderrsn, where the total of the sum* > 0, using the folderrsn, duedate, yearforbilling, installmentnumber, and an expression (Outstanding) to sum the sum* columns.

    Using Instalment Balances as the basis for a crosstab query, I am able to list the folderrsns as rows, and the yearforbilling as columns, and a grand total by folderrsn. The problem, it contains too many year columns.

    The requirements for reporting, are to show current year (2006), 1st previous year (2005), 2nd previous year (2004), and 3rd+ previous year sum (2003+2002+etc...), along with a grand total (as below).

    <table border=1><td>FOLDERRSN</td><td>2006</td><td>2005</td><td>2004</td><td>2003 & PREVIOUS</td><td>Total of Outstanding</td><td>147852</td><td>500.00</td><td>900.00</td><td>1100.00</td><td>11,300.00</td><td>13,800.00</td><td>365214</td><td>400.00</td><td>700.00</td><td>1000.00</td><td>11,200.00</td><td>13,300.00</td></table>

    My question is, how can I report this as shown? Is it a design in my crosstab or 'base' query, or is it a form / report / other I have to do?

    Any help you can provide would be wonderful!
    Christopher Baldrey

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

    Re: Crosstab query - detail and summary together (2000

    You can use an expression like this as column header field for the crosstab query:
    <code>
    Y: IIf([YearForBilling]>2003,[YearForBilling],"2003 & Previous")
    </code>
    By default, this will display the columns in ascending order, but you can change this by explicitly specifying the Column Headings property of the query:
    - Open the crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Set the Column Headings property to
    <code>
    "2006","2005","2004 & Previous"
    </code>
    Unfortunately, next year you'll have to add 2007 manually.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query - detail and summary together (

    Thanks Hans, that's slick!

    Of course, automating this would be much better for next year, and those afterwards. Can I setup a parameter in one of the queries, or coding to say "current year", and calculate the values that would create the "right" years?

    I'm thinking,
    <code>
    IIf(<!t>[YearForBilling]<!/t>><!t>[CurrentYear - 3]<!/t>,<!t>[YearForBilling]<!/t>,<!t>[CurrentYear - 3]<!/t> + " & Previous")
    </code>
    I'm not too concerned with the sort order on the columns. Ascending order would be acceptable to our end users I think.
    Christopher Baldrey

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

    Re: Crosstab query - detail and summary together (

    If you don't mind the ascending order, leave the Column Headings property blank, and use the following expression:
    <code>
    IIf([YearForBilling]>Year(Date())-3,[YearForBilling],(Year(Date())-3) & " & Previous")
    </code>
    Date() returns the current date, and Year(Date()) extracts the year from this date.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query - detail and summary together (

    That's perfect Hans. That appears to give me what I'm looking for.

    Whoohoo!
    Christopher Baldrey

Posting Permissions

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