Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Red face Unanswered: Need help in a Query

    Hi Frenz,

    I need some help with an SQL Qry.

    Dept:

    Dept_id Dept_name
    -----------------------------
    1 Industrial
    2 Retail
    3 Valuation

    Tbl 1:

    Dept Total
    ---------------------
    Valuation 11,000
    Retail 20,000

    Tbl2:

    Dept Total
    ---------------------
    Retail 15,000
    Industrial 30,000

    Tbl3:

    Dept Total
    ---------------------
    Valuation 31,000

    Tbl4:

    Dept Total
    ---------------------
    Valuation 11,000
    Retail 20,000
    Industrial 4,00,000


    I want the Resulting table to be as

    Dept Total1 Total2 Total3 Total4
    --------------------------------------------------------------------
    Industrail 0 30,000 0 4,00,000
    Retail 20,000 15,000 0 20,000
    Valuation 11,000 0 31,000 11,000

    This looks like a join of many tables but I don't know how to get it structured as such and place zero where there is no correspding value.

    Thanks for the patients to help.

    Micky

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Given your required result set, this is a VERY bizarre table layout.

    Are all 4 tables the exact same layout?

    If so, is there a specific reason you need to have 4 seperate tables?

  3. #3
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi I have attached a txt file with the table .
    I found a method with Oracle using decode but cannot use it in access.

    Please help me with this Query as it is urgent and overdue for long

    Thanks a lot

    Micky
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select NZ(columnA, 0)

    where columnA may contain null values
    Last edited by r123456; 04-06-04 at 08:36.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Thank you very much guys it helped especially the NZ () function.

    Thanks again

Posting Permissions

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