Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Help with report and Query

    I have this query running in a report but what im trying to do is have one column showing the grouped results under one heading for each, the next column showing how many of that heading its found and the third showing from those results how many has a field with a 2 character code documented.

    SELECT workflow.Source, Count(workflow.Source) AS NoContactsBySource
    FROM workflow
    WHERE (((workflow.[Preferred Contact Date]) Between [Enter Start Date] And [Enter End Date]))
    GROUP BY workflow.Source;

    so it should look like this

    Source
    Opportunities Completed

    Internet 3 1
    Telephone 4 3
    Face to Face 2 0

    Ive got the first two columns sorted but i need it to find out from those results in the first 2 columns how many have actually been completed, we document the completed records by entering initials of the person who did it.

    thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Add the following to your select list
    Code:
    , Sum ( Iif(initials_of_who_dunnit > '', 1, 0) ) As [completed]
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    Thank you very much georgev

    P.S how would i also add another criteria to it where it counts if the check box has been ticked from the above results?
    Last edited by gmc2k2; 10-06-08 at 10:40.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  5. #5
    Join Date
    Sep 2008
    Posts
    101
    thanks georgev again.....one more question if possible
    my next column would be percentage results...i.e
    Internet 3 1
    Telephone 4 3
    Face to Face 2 0

    then the next colum would show:
    33%
    75%
    0%
    thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    <if 2> / <if 1> * 100 As [percent]
    Where <if 1> and 2 are just a repeat of the code you've already written.
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2008
    Posts
    101
    thanks georgev
    Last edited by gmc2k2; 10-06-08 at 18:40.

Posting Permissions

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