Find Missing Number in Sequence per Record

[escuro19]

Hello,

I have a table where each Volunteer start from Day 1 to n...(Sequence)for example:

Volunteer | Day
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2

I am hoping if somone could help, i want to be able to loop through a Volunteer' Day, to find missing sequence based on the volunteer. eg. the user specify which Volunteer they want to see (using Textbox) then it will bring up just that volunteer and the missing their sequence:

Volunteer | Day | Error
1 1 ok
1 2 ok
1 4 Error
1 5 ok

If possible output it in a datasheet (table).

Thankyou in advance

 

[MajP]

data

volunteer	intDay
1	1
1	2
1	3
1	4
1	5
2	1
2	2
2	3
2	5
3	1
3	3
3	5
3	7

Do not use Day as a field name it is a reserved word

qryIDError

SELECT 
  A.volunteer, 
  A.intDay, 
  (Select MAX( intDay) from tblVolunteer where A.volunteer = tblVolunteer.volunteer AND tblVolunteer.intDay < A.intDay) AS PreviousValue
FROM 
  tblVolunteer AS A
WHERE 
  ((Not ((Select MAX( intDay) from tblVolunteer where A.volunteer = tblVolunteer.volunteer AND tblVolunteer.intDay < A.intDay)) Is Null And ((Select MAX( intDay) from tblVolunteer where A.volunteer = tblVolunteer.volunteer AND tblVolunteer.intDay < A.intDay))<>[A].[intDay]-1))
ORDER BY 
  A.volunteer, A.intDay;

returns

volunteer intDay PreviousValue
2	5	3
3	3	1
3	5	3
3	7	5

use in qryErrorOK

SELECT 
 tblVolunteer.volunteer, 
 tblVolunteer.intDay, 
 IIf(Not IsNull([PreviousValue]),"Error","OK") AS ErrorOk
FROM 
 tblVolunteer LEFT JOIN qryIDError ON (tblVolunteer.intDay = qryIDError.intDay) AND (tblVolunteer.volunteer = qryIDError.volunteer);

returns

volunteer intDay ErrorOk
1	1	OK
1	2	OK
1	3	OK
1	4	OK
1	5	OK
2	1	OK
2	2	OK
2	3	OK
2	5	Error
3	1	OK
3	3	Error
3	5	Error
3	7	Error

 

[escuro19]

Wow Thankyou so much! works perfectly. You are god to me.