Results 1 to 3 of 3

Thread: query

  1. #1
    Join Date
    Mar 2008
    Posts
    29

    Red face Unanswered: query

    hi all,
    Thank you to all for quick responces. I got one more doubt.
    I had one query as

    SELECT LEFT( AC_NO, 3 ), SUM(CR_AMT), SUM(DR_AMT)
    FROM Z
    WHERE (LEFT( AC_NO, 1 ) = '3' OR LEFT( AC_NO, 1 ) ='4')
    GROUP BY LEFT( AC_NO, 3 ) ORDER BY LEFT( AC_NO, 3 ) ;

    I am gettin result as:

    EXPR1001 EXPR1002
    =========== ===========
    0.0 0.0
    EXPR1000
    ========
    301
    0.0 0.0
    EXPR1000
    ========
    303
    0.0 0.0
    EXPR1000
    ========
    401
    0.0 100.0
    EXPR1000
    ========
    410


    But i want the result as :


    EXPR1000 EXPR1001 EXPR1002
    ========================= =========== ===========
    301 0.0 0.0
    303 0.0 0.0
    401 0.0 0.0
    410 0.0 100.0


    By runing the query the result of one column taking as long string.
    If it is taking like that i am getting problum to fetch the data in the frontend.
    Can any one help me. I am new to Access. I tried but not getting.
    Thanks in advance.
    Last edited by suni; 03-28-08 at 06:28.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    IF I am understanding you, you might want to try this:

    Code:
    SELECT LEFT( AC_NO, 3 ) & " " &  SUM(CR_AMT) & " " & SUM(DR_AMT)
    FROM Z
    WHERE (LEFT( AC_NO, 1 ) = '3' OR LEFT( AC_NO, 1 ) ='4')
    GROUP BY LEFT( AC_NO, 3 ) ORDER BY LEFT( AC_NO, 3 ) ;
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2008
    Posts
    29

    Red face query

    Hi StarTrekker,
    Thank you for your reply.
    But usually i am using sqlbase. Now i have to convert my application to acces as backend. In sqlbase when i run the query i will get the result side by side if i give coma.
    But when i run in Access i am getting correct data but few columns are taking as long string so few columns data coming down. If it so i will get problum to retreve data through front end.

    I mean if i used any function like LEFT, RIGHT...
    This problum i am getting. Few columns data coming down.

    I need to display data in row, now few columns data coming down.
    I hope i am not disturbing you.
    I hope you understood my problum.
    Can you help me please.

Posting Permissions

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