?Query that extracts all records entered over consecutive dates.

[turtsmurt]

Hello all,

**ROOKIE**

Have a simple query question. I have a table that records everytime an employee has attended a case (by case id#). I have created the below query to extract how many times an employee has attended a case with less than 2 days notice. I have all the criteria that I need here, however, I only want to extract the records that DO NOT include those cases where the employee attended the same case over consecutive dates.

I am not sure how to tell my query to look at the case number and employee number and exclude those cases that occurred over consecutive dates. I'm thinking that I will have to create a query for this and then use the below query.

Can anyone tell me how to create a query that just excludes all records that occur over consecutive dates?

SELECT tblNOTICE.OfficerPayroll, tblNOTICE.DCNumber, [CourtDate]-[DateSent] AS DIFF, [tblCOURTCODES-GROUPS].[COURT CODE DESCRIPTION], [tblCOURTCODES-GROUPS].[COURT LOCATION GROUP], tblNOTICE.CourtDate, tblNOTICE.DateSent, tblNOTICE.CancelFlag
FROM [tblCOURTCODES-GROUPS] INNER JOIN tblNOTICE ON [tblCOURTCODES-GROUPS].[CODE NUMBER] = tblNOTICE.AltCrm
WHERE ((([CourtDate]-[DateSent])<3) AND ((tblNOTICE.CourtDate) Not In ([CourtDate]+1)) AND ((tblNOTICE.CancelFlag) Is Null))
ORDER BY tblNOTICE.DCNumber, tblNOTICE.CourtDate;

Any help is always appreciated.

Christine

 

[turtsmurt]

*bump*