Count number of record in a certain time frame 1

[yoshi88]

Hi, I have an employe database with details on everyday of those employes. There is an a particular event that I want to track by date. If the employe has been late 4 times during a period of 28 days I want the report or the query to show it to me.

I was thinking of something like that in a query:

IdEmploye Decription Date NumberIn28Days
1234 Late 01/01/2004 4
1234 Late 05/01/2004 3
1234 Late 18/01/2004 2
1234 Late 24/01/2004 2
1234 Late 20/02/2004 1

For the period starting 01/01/2004 there is 4 records that are in a 28 days time frame but for the period starting 24/01/2004 there is only 2. Basically I want the query to calculate for every record, how many other records there is in the 28 next days.

How can I do that if it is possible.


Frank

 

[Blorf]

Assuming you have your data in a table called Lates, this should work.


SELECT Lates.IdEmployee, Lates.Description, Lates.Date, 1+DCount("IdEmployee","Lates","[IdEmployee]='" & [IdEmployee] & "' And [Date]>#" & [date] & "# And [Date]-#" & [date] & "# < 28") AS [Count]
FROM Lates;


ChaZ

 

[yoshi88]

Hi, thanks for the help but it's only giving me a result of 1 for every record. I think Access doesn't recognize the # part before and after the [date].

Also, what is the difference between [Date] and [date]. If there is any my computer consider that they are the same.

Thanks

Frank

 

[Blorf]

No difference. Typo error on my part.

Your date field is a date type in your table?

ChaZ

 

[PHV]

Something like this ?
SELECT A.IdEmployee, A.Description, A.Date
,(SELECT Count(*) FROM Lates B WHERE B.IdEmployee=A.IdEmployee And B.Date-A.Date<=28) AS NumberIn28Days
FROM Lates A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[yoshi88]

Thanks again PHV,

Your reply are really appreciated. I had to modify the subquery a bit to make it work like I want:

SELECT A.IdEmployee, A.Description, A.Date
,(SELECT  Count(*) FROM Lates B WHERE B.IdEmployee=A.IdEmployee And B.Date>=A.Date And B.Date-A.Date<28) AS NumberIn28Days
FROM Lates A;

Frank