Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: query and report problem please help

    I run a query that gives me the following columns
    workorderno, downtime, mttr, workstatus, deptgroup, datereceived

    I need to display the following values in a report. How do I do it, could some please guide me through the steps and the code

    dt = sum(downtime) where deptgroup = 1 and datereceived between sd and ed
    pdt = sum(downtime) where deptgroup = 2
    wo = count(workorderno) where status in (1,2) and deptgroup = 1

    totaldt = dt + pdt

    Thank you

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Look into using iif() in your sum calculations. Eg:

    =sum(iif([downtime] BETWEEN [sd] AND [ed], [downtime], 0))

    etc.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    dt = sum(downtime) where deptgroup = 1 and datereceived between sd and ed

    so the above could be put in a text box in the report as

    dt = sum(iif(downtime), deptgroup =1, datereceived between sd and ed)

    I dont think the syntax is correct
    could you please help me out with that

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Close:

    dt = sum(iif(deptgroup =1, iif(datereceived between sd and ed, downtime, 0), 0))

    Just follow the logic.

    if the deptgroup field is = 1, then evaluate the next statement: If daterecieved is between sd and ed, then we'll be summing the downtime field, if either condition fails, it will be counted as 0
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by Teddy
    Close:

    dt = sum(iif(deptgroup =1, iif(datereceived between sd and ed, downtime, 0), 0))

    Just follow the logic.

    if the deptgroup field is = 1, then evaluate the next statement: If daterecieved is between sd and ed, then we'll be summing the downtime field, if either condition fails, it will be counted as 0
    Hey, that was very good Teddy...the way you gave an example, but then actually explained what it meant. That's the way I think others should be helped so that later on they know what they are actually doing.
    hats off to ya...keep up the good work

    Bud

  6. #6
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    yeah thanks a lot teddy
    that worked great for me

Posting Permissions

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