Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Place a zero on Total query count field (2003)

    Hi! This is a little difficult task that it is killing me. I have a course table that has a Primary key CourseID field and it has a one-to-many relationship with referential integrity with “candidates table” which the field CourseID is included. I created a total query which counts CandidateStatus field, according to a “cancelled” criteria as well as the status of the course. The total query displays the count of candidates for the courses that have participant’s information in the candidates table but it does not display the CourseID of the course which is in the course table without any participants information in the candidates table yet.

    This is a SQL view of the Design view query

    SELECT tblDataCourseManagement.CourseID, tblDataCourseManagement.CourseStatus, tblDataCourseCandidates.CandidateStatus, Count(tblDataCourseCandidates.CandidateStatus) AS CountOfCandidateStatus
    FROM tblDataCourseManagement INNER JOIN tblDataCourseCandidates ON tblDataCourseManagement.CourseID = tblDataCourseCandidates.CourseID
    GROUP BY tblDataCourseManagement.CourseID, tblDataCourseManagement.CourseStatus, tblDataCourseCandidates.CandidateStatus
    HAVING (((tblDataCourseManagement.CourseStatus)="CANCELLE D") AND ((tblDataCourseCandidates.CandidateStatus)="CANCEL LED"));


    How can I make the total query display a zero on the CandidateStatus count for the course that doesn’t have any participant information yet, so it can be display in the query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Place a zero on Total query count field (2003)

    Create a new query in design view.
    Add tblCourseManagement and the totals query to the new query.
    Join them on CourseID, double click the join line and select the option to display ALL records from tblCourseManagement.
    Add the CourseID field from tblCourseManagement to the query grid,
    Create a calculated column

    StatusCount: Nz([CountOfCandidateStatus],0)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Place a zero on Total query count field (2003)

    Hans, you did it again! your guidance did exactly what I was looking for. Thanks #1000000

Posting Permissions

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