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

Question

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

ritesh-385882

Mr or Mrs. 500

Points: 533
More actions
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;

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

ritesh-385882 Mr or Mrs. 500 Points: 533 More actions · Answer 2

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

CountOfPersonnelNumberPersonnelNumberSickEpisodeIDFirstDate LastDate

100216922220/09/200022/09/2000

100216922328/12/200001/01/2001

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

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

ritesh-385882 Mr or Mrs. 500 Points: 533 More actions · Answer 4

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

Thanks ,regards

Ritesh

mains Grasshopper Points: 10 More actions · Answer 5

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.

ritesh-385882 Mr or Mrs. 500 Points: 533 More actions · Answer 6

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

ijaz SSCommitted Points: 1912 More actions · Answer 7

You can use the ISNULL function provided by the system;

Here is the example

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

cheers

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

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?