Results 1 to 4 of 4

Thread: count query

  1. #1
    Join Date
    Feb 2009
    Posts
    9

    Unanswered: count query

    how can i get the count of the number of particular clients with two WHERE clauses?

    e.g.
    SELECT Count(tblGenInfo.Lastname) AS "Number of CLients"
    FROM tblGenInfo
    WHERE (((tblGenInfo.Hospital)="Abella Midway Hospital"))
    GROUP BY [tblGenInfo.Hospital], tblGenInfo.DateofSurvey
    HAVING (((tblGenInfo.DateofSurvey) Between #2/1/2008# And #1/2/2010#))
    ORDER BY [tblGenInfo.Hospital];

    because as i've tried it, it will give the count on separate columns if you use two WHERE clause. the result will be:

    Number of Clients
    1
    1
    1
    1

    instead of:

    Number of Clients
    4

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    try WHERE (((tblGenInfo.Hospital)="Abella Midway Hospital")) AND (((tblGenInfo.DateofSurvey) Between #2/1/2008# And #1/2/2010#))

  3. #3
    Join Date
    Feb 2009
    Posts
    9
    nothing changes, it still gives the same result

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Try

    SELECT Count(tblGenInfo.Lastname) AS "Number of CLients"
    FROM tblGenInfo
    WHERE (((tblGenInfo.Hospital)="Abella Midway Hospital") AND ((tblGenInfo.DateofSurvey) Between #2/1/2008# And #1/2/2010#));

    With your GROUP BY statement GROUP BY [tblGenInfo.Hospital], tblGenInfo.DateofSurvey by using the two fields in your output ,this will show a count for each Group By e.g. Abella Hospital 2/1/2008 total 1, Abella Hospital 2/2/2008 total 1, etc. If you do not show the Hospital and Survey as output e.g. using the group by, then the result will give you the total count.

    Also you do not need ORDER BY [tblGenInfo.Hospital] as there is only one hospital as per your WHERE criteria.

Posting Permissions

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