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

    Unanswered: Joining 2 Queries

    I i got 2 queries that return these values:

    Query 1:
    --------------

    Dept Sum(1month)
    -------------------------
    1 1200

    3 1500

    4 3600

    Query2 :
    ------------

    Dept Sum(2month)
    -------------------------
    1 2200

    2 1600

    4 1400

    Can i have a join upon these to get a final result as

    Dept Sum (1month) Sum (2month)
    ---------------------------------------------

    1 1200 2200
    2 1600
    3 1500
    4 3600 1400

    Please assist on this Query.

    Thanks
    Michael

  2. #2
    Join Date
    Dec 2003
    Posts
    13
    hi

    you just tell me these two querys from same table or from different table

    I am waiting to your repely , I will help

    regards
    Nanaiah

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    Have a look at Rockeys answer to my posting here, think that is about what you want, the use of either IIF or NZ:

    http://www.dbforums.com/t978670.html

    kedaniel

  4. #4
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi guys,

    These come from a same Table called invoice,which has all datas.

    Invoice table has info as:

    Inv_number Date Amount Department Month....

    001 12/12/2003 12000 Retail 1 Month
    002 12/11/2003 12000 Retail 2Month
    003 12/10/2003 12000 Industrial 3 Month
    004 12/10/2003 12000 Retail 3 Month
    005 12/11/2003 12000 Industrial 2 Month
    006 12/11/2003 15000 Industrial 2 Month


    The first Query retrives:
    Dept Sum(Amount) of 1 month

    Retails 12000


    The second Query Return :

    Dept Sum (Amount) of 2 month
    Retail 12000
    Industrial 17000

    The 3rd Query return:

    Dept Sum (amount) of 3 month
    Retail 12000
    Industrial 12000

    The final answer i would like to have is

    Dept 1 month 2 month 3 month >3 month

    Retail 12000 12000 12000 ...................
    Industrail 17000 12000 .................

    Thanks for your quick responce

    Thanks Michael

  5. #5
    Join Date
    Dec 2003
    Posts
    13
    hi

    if you are using MS SQL please try this following query I hope you will
    get result

    select
    department,
    case
    when month =1month then sum(amount)
    end as 1month ,
    case
    when month =2month then sum(amount)
    end as 2month ,
    case
    when month =3month then sum(amount)
    end as 3month ,

    from invoice
    group by 1

    if you are using other database let me know

    regards
    nanaiah

  6. #6
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200
    Hi,

    I am using MS Access

    Thanks Michael

  7. #7
    Join Date
    Dec 2003
    Posts
    13
    hi

    I hope In MS ACCESS same query may work

    if any error let me know
    regards
    nanaiah

Posting Permissions

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