Sign in to follow this  
Followers 0

Consecutive Events

8 posts in this topic

Posted

Dear Community,

I have a table containing records such as:

ID Date Name Attendance

01 01/01/2012 ABC Yes

02 01/01/2012 DEF Yes

03 02/01/2012 ABC Yes

04 02/01/2012 DEF No

05 03/01/2012 ABC No

06 03/01/2012 DEF No

07 04/01/2012 ABC No

08 04/01/2012 DEF No

I want to count consecutive absences of a particular name by dates. i.e Has DEF been absent consecutively for 3 days?

I found something on http://www.briandunning.com/cf/69 but I am not able get it implemented.

Please help...

Share this post


Link to post
Share on other sites

Posted

It's not quite clear from your example what "consecutive"means. Would DEF be considered "absent consecutively for 3 days" in the following example?

01 01/01/2012 ABC Yes

02 01/01/2012 DEF Yes

03 08/01/2012 ABC Yes

04 08/01/2012 DEF No

05 15/01/2012 ABC Yes

06 15/01/2012 DEF No

07 22/01/2012 ABC Yes

08 22/01/2012 DEF No

09 29/01/2012 ABC Yes

10 29/01/2012 DEF Yes

It would also help to know why you need this - and when (in what context).

Share this post


Link to post
Share on other sites

Posted

I am working on an attendance system. Those who are have not attended consecutive 3 or more expected events will be sent a warning e-mail.

In the above example, DEF has not attended 3 expected events consecutively if sorted by date (08, 15, 22). I want him to be listed in the report also reporting how many consecutive he left, (maybe he has left 4 consecutive dates)...

Share this post


Link to post
Share on other sites

Posted

Those who are have not attended consecutive 3 or more expected events will be sent a warning e-mail.

Is there a table of the events they are supposed to attend? That would make it easy to get the IDs of n previous events and establish a relationship to their attendance records.

Otherwise you'd need a "cascading" relationship, which could get slow with a large number of records.

Share this post


Link to post
Share on other sites

Posted

I have a table "attendance marking" which sends attendance record to another table "core attendance".

"attendance marking" table sends ID, Date, Name, Attendace as fields to the "core attendance" table.

I want report derived from "core attendance" uniquely showing only those names which have consecutively "No" sorted by date in "Attendance" field for 3 or more than 3 times.

Share this post


Link to post
Share on other sites

Posted

You haven't answered my question.

Share this post


Link to post
Share on other sites

Posted

Is there a table of the events they are supposed to attend? That would make it easy to get the IDs of n previous events and establish a relationship to their attendance records.

Otherwise you'd need a "cascading" relationship, which could get slow with a large number of records.

Yes, there is a table of events. (which records if someone attended or not with unique id number allocated to each person.)

Share this post


Link to post
Share on other sites

Posted

Yes, there is a table of events. (which records if someone attended or not

That would be an Attendance table, not an Events table. The Events table would have one unique record for each event.

To put it another way, you cannot tell if someone missed 3 consecutive events unless you have a list of the events they were supposed to attend, alongside a list of the events they did attend.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0