Post reply

How can i match where the personnel number shows at least 4 times HELP?

  • October 29, 2007 at 4:52 am

    #70439

    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 29, 2007 at 5:30 am

    #746178

    I don't know whether the below queries would run in Access or not...(and i'm sure the last one would work)...

    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") AND CountOfPersonnelNumber >= 4 )

    ORDER BY tblSicknessEpisode.FirstDate;

    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") AND Count(tblSicknessEpisode.PersonnelNumber) >= 4 )

    ORDER BY tblSicknessEpisode.FirstDate;

    --Ramesh

  • October 29, 2007 at 5:58 am

    #746197

    That will not work as I want to get a count of the personnel no.

    This shows the personnel no twice. but the count result is 1 . it should show 2 and therefore one row as the personell numbers match. Any help! regards Rk

    CountOfPersonnelNumber PersonnelNumber SickEpisodeID FirstDate LastDate

    1 002169 222 20/09/2000 22/09/2000

    1 002169 223 28/12/2000 01/01/2001

  • October 29, 2007 at 6:02 am

    #746201

    Well, then you've to remove the additional columns added in group by...

    SELECT Count(tblSicknessEpisode.PersonnelNumber) AS CountOfPersonnelNumber, tblSicknessEpisode.PersonnelNumber

    FROM tblSicknessEpisode

    GROUP BY tblSicknessEpisode.PersonnelNumber

    HAVING (((tblSicknessEpisode.SickCategory)="NWR") AND CountOfPersonnelNumber >= 4 )

    ORDER BY tblSicknessEpisode.PersonnelNumber;

    --Ramesh

  • October 29, 2007 at 7:11 am

    #746231

    thanks very much Ramesh this has helped..I might have to bother you with further question later on.

    Thanks ,regards

    Ritesh

  • October 30, 2007 at 6:26 am

    #746597

    If you are using SQL Server 2005 then there is a new ranking function that will help you. If you are using SQL Server 2005 then I have an example of that code.

  • October 30, 2007 at 7:26 am

    #746630

    quick question hope you can help. In access I hava table that contains a last date.. Some records have a null value..

    I'm writing a query to say if the date is null then use todays date. How do I write this in a query

    Regards

    ritz

  • October 30, 2007 at 7:37 am

    #746635

    You can use the ISNULL function provided by the system;

    Here is the example

    SELECT ISNULL([Last Date], GETDATE()) AS [LAST DATE]

    cheers

  • October 30, 2007 at 8:36 am

    #746664

    Careful there....

    In Access' version of SQL - ISNULL takes only one parameter and tests the value to see if it IS null...

    The Access equivalent of SQL's ISNULL function is NZ.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 9 posts - 1 through 8 (of 8 total)

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