Post reply

Matching the duplicate records and doing a count HELP?

  • October 29, 2007 at 4:44 am

    #73785

    The code below is the query .

    what im trying to do is to get all the personnelNumbers that occur greater than or equal to 4.

    Help would be apprecated .Thanks Ritz

    SELECT Count(tblSicknessEpisode.PersonnelNumber) AS CountOfPersonnelNumber, tblSicknessEpisode.PersonnelNumber, tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.FirstDate, tblSicknessEpisode.LastDate, tblSicknessEpisode.SickCategory

    FROM tblSicknessEpisode

    GROUP BY tblSicknessEpisode.PersonnelNumber, tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.FirstDate, tblSicknessEpisode.LastDate, tblSicknessEpisode.SickCategory

    HAVING (((tblSicknessEpisode.SickCategory)="NWR"))

    ORDER BY tblSicknessEpisode.FirstDate;

  • October 31, 2007 at 8:06 am

    #747090

    Try this..

    SELECT Count(tblSicknessEpisode.PersonnelNumber) AS CountOfPersonnelNumber, tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.FirstDate, tblSicknessEpisode.LastDate, tblSicknessEpisode.SickCategory

    FROM tblSicknessEpisode

    GROUP BY tblSicknessEpisode.SickEpisodeID, tblSicknessEpisode.FirstDate, tblSicknessEpisode.LastDate, tblSicknessEpisode.SickCategory

    WHERE

    tblSicknessEpisode.SickCategory="NWR"

    HAVING (((tblSicknessEpisode.PersonnelNumber)>=4))

    ORDER BY tblSicknessEpisode.FirstDate;

    Having is meant to apply condition on any Aggregate functions used with Group By, in the above example u can use WHERE rather than Having.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply