Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    56

    Unanswered: Query - Using a count

    I have two tables. A Staff Table (TblStaff) and a Job Table (TblJob).

    I have a query that returns into a list box the name of the staff member some basic details abou them and then the total number of jobs that they have assigned to them that are yet to be completed. The field in the TblJob is called Completed (no surprises) and is filled in by either Yes or No.

    When I use the Count function in the Query for the 'Completed' field is returns all the jobs and not just the ones that are completed, ie have the entry of YES. I have added the word Yes to the Criteria section in the query and it does not make a difference.

    Any ideas please

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT SUM(IIF([Completed]=True,1,0)) AS CompleteCount FROM ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's the SQL for the query?
    oh yeah... documentation... I have heard of that.

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

  4. #4
    Join Date
    Jul 2004
    Posts
    56

    Thanks

    M Owen .. Thanks for the quick response. Does that line of text go in the Criteria or Where else can I put it and what needs to go after the FROM...

    Thanks


    Col_bol

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by col_bol
    M Owen .. Thanks for the quick response. Does that line of text go in the Criteria or Where else can I put it and what needs to go after the FROM...

    Thanks


    Col_bol
    Well in your case it's a column in your query ... If you right click select on the SQL view you can see the SQL statement generated ... Where you have the "Completed" column substitute the "SUM(IIF([Completed]=True,1,0)) AS CompleteCount" ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jul 2004
    Posts
    56

    Thanks

    Thanks - It worked great

    Col_bol

Posting Permissions

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