Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Question Unanswered: Need help count and totalling?

    Hi All,

    Can someone direct me in the right direction?

    I have a table tblDrugScreen, DSID, CompanyID, Reason, FinalReslts. I need to generate a report that shows:

    COMPANY NAME
    Reason for Testing Total Positive Negative
    PreEmp 24 4 20
    Random 25 5 20
    PostAC 10 1 9

    I have tried to put this in a query but am having problems with the count()
    SELECT Count(*) AS NUM, tblDrugScreen.Reason
    FROM tblDrugScreen
    GROUP BY tblDrugScreen.Reason;
    This works fine and gives me the total, but I cannot figure out how to break out the Positive & negatives.
    I then thought about just building a report using the DCOUNT()
    =DCount("[FinalResults]","tbldrugscreen","[Reason] = 'PreEmp'")
    This again gives me the TOTAL, but still cannot break out POS/NEG’s.

    What am I doing wrong?

    Thanks for your help

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try something like:

    SELECT Count(*) AS NUM, tblDrugScreen.Reason, Sum(IIf(FinalResults = "Positive",1,0)) AS TotalPositive, Sum(IIf(FinalResults = "Negative",1,0)) AS TotalNegative
    FROM tblDrugScreen
    GROUP BY tblDrugScreen.Reason;
    Paul

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    WOW....I have so much to learn. You make it look so easy!!!
    Thanks a million Paul, It worked just fine.
    Enviva....

  4. #4
    Join Date
    Feb 2004
    Posts
    67
    Paul,
    Your code counts the number of tests (In groups), and breaks out the POS/Neg. How do I get the Companies (ContactID) tied to this query, or do I need to create another query? I need the above query sorted for each company.
    Report needs to be:
    COMPANY-1
    ......REASONS........TOT........POS........NEG
    ..........PReEmp........24..........4...........20
    ..........Random........25..........5...........20
    COMPANY-2
    ......REASONS........TOT........POS........NEG
    ..........PReEmp........20..........4...........16
    ..........Random........35..........5...........30
    Thanks again...Enviva

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try adding the field to both the SELECT and GROUP BY, like:

    SELECT Count(*) AS NUM, tblDrugScreen.Reason, Sum(IIf(FinalResults = "Positive",1,0)) AS TotalPositive, Sum(IIf(FinalResults = "Negative",1,0)) AS TotalNegative, CompanyID
    FROM tblDrugScreen
    GROUP BY tblDrugScreen.Reason, CompanyID;
    Paul

  6. #6
    Join Date
    Feb 2004
    Posts
    67
    Thanks...Works great!

Posting Permissions

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