Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: help Buidling Query thanks!!!

    If anyone can help me build this query you will solve a problem ive been working on ALL day.

    I have a table which has for simplicity sake OperatorID, TransActionAmt, TransCode

    What I want is this:

    OPID, SUMTransActionAmtBONUS, SUMTransActionAmtALL, CountTransActionAmtBONUS, CountTransActionAmtALL

    so the totals of all CODES, and then the totals of only those codes where the code is BONUS.

    end of day would look like this
    12345 | 200 | 2000 | 3 | 17
    12222 | 100 | 6723 | 1 | 6

    and so on...


    Code:
    SELECT [FEB].OPID, OPLIST.LOCATION, OPLIST.EMPLOYEELASTNAME, OPLIST.EMPLOYEEFIRSTNAME, Count([FEB].ACTV_AMT) AS CountOfACTV_AMT, Sum([FEB].ACTV_AMT) AS SumOfACTV_AMT
    FROM OPLIST RIGHT JOIN [FEB] ON OPLIST.AMDOCS = [FEB].OPID
    WHERE ((([FEB].ACTV_AMT)>0))
    GROUP BY [FEB].OPID, OPLIST.LOCATION, OPLIST.EMPLOYEELASTNAME, OPLIST.EMPLOYEEFIRSTNAME;
    The following SQL would get me based on the code.

    SO I want the sum and count from the second query in the first query I cant generate both and then union because of the right join where some of the data is missing, this needs to happen all in one go.
    Any ideas?

    Code:
    SELECT [FEB].OPID, OPLIST.LOCATION, OPLIST.EMPLOYEELASTNAME, OPLIST.EMPLOYEEFIRSTNAME, Count([FEB].ACTV_AMT) AS CountOfACTV_AMT, Sum([FEB].ACTV_AMT) AS SumOfACTV_AMT
    FROM OPLIST RIGHT JOIN [FEB] ON OPLIST.AMDOCS = [FEB].OPID
    WHERE ((([FEB].ACTV_AMT)>0) AND (([FEB].ACTV_REASON_CODE)="BONUS"))
    GROUP BY [FEB].OPID, OPLIST.LOCATION, OPLIST.EMPLOYEELASTNAME, OPLIST.EMPLOYEEFIRSTNAME;
    I hope this can be done, because this is beocming a nightmare
    Last edited by Raysoc; 04-08-09 at 16:52.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My approach would be this:

    Qry1:
    OPID
    IIf(TransCode = "BONUS", TransAmt, 0) as BonusAmount
    TransAmt as Amount
    IIf(TransCode = "BONUS", 1, 0) as BonusTally
    1 as Tally

    Qry2:
    Based on Qry1, Group on OPID, sum everything else.

    Unless I've misinterpreted what you want to do
    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

Posting Permissions

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