I hope everyone's having a good Friday. Thank you kindly to anyone kind enough to offer some advice...
I have a table of attendance data for children attending a club. This table (T_Attendance) records the AttendanceID
(autonumber) ChildID, Date and whether they attended or not. I'd like to use this data to create a log of when each child
started / stopped and restarted coming to the club. This data should be recorded in a table called T_Actions, which records:
ActionID (autonumber, primary key)
ActionType (number: 1 for started, 2 for quit, 3 for restarted)
I would like to create a query or piece of VBA code (I'm not fussy which, it only needs to run once), which for each Child
will go through the attendance records in order of date, and perform the following actions:
If the current record is the final record for this child AND the date is more than 30 days earlier than NOW, Append a record
to T_Actions to say child has quit (use the day after the current record's date as ActionDate).
If the current record is not the final record for this child AND the difference between the date of the current record and
the date of the next record is greater than 40 days Update T_Actions to say child quit (use the day after the current record
as the date). Add a further record showing that they restarted on the date of the following record.
I only have accurate attendance records in the database since 1st July 2009, so this query should ignore any records with
dates prior to that.
I get the feeling this should be quite simple to accomplish, however I'm not very sure how to begin with this so I'd really
appreciate any help.