My unsolved DB challenge: track attendance as membership and roles changes over time

[JL512]

I am trying to sketch out and program my first Access (v2010) database and may have sought a too ambitious project. In essence, I want to track membership attendance at meetings of a relatively small organization (about 15 people) comprised of 6 committees (each with 3 – 5 members and members serve on more than 1 committee). A much larger and real world example would be the U.S. Senate or House of Representatives. The database logs:

• Member
• Date joined organization
• Date left organization (if no longer active) (*it's possible to return)
• Organization chairman (one person) (by date)
• Organization meeting attendance (all members) (by date)
• Committees
• Committee members (select members) (by date)
• Committee chairman (one person) (by date)
• Committee meeting attendance (by date)
• Non-members
• Non-member attendance at organization meetings
• Non-member attendance at committee meetingss

Having read books and various online forums and watched video tutorials, I understand that an append query would serve to update the database for meeting attendance. For example, if a form input includes a meeting type field (i.e., committee "X") and a date field, a background lookup would produce a combo box (or other appropriate selection tool) with the members of that committee "X" on that particular date. Then, meeting attendance (in person, on the phone or absent) can be logged.

I could probably build the tables and create the relationships if the membership of the organization and the committees and chairman assignments were static. But, they are not. Each year assignments to committees change as do the committee chairmanships. Members may serve on a particular committee in, for example, 2011 to 2012, then move to another committee, and return to the original committee in 2013 to current. Members may even leave the organization completely and then return.

A member’s record, identifiable by his/her name and a primary key member ID, should be unique and not repeated. It would track his/her dates of membership in the organization as a whole, committee assignments over time, any organization or committee chairmanships over time, and all required meeting attendance.

I cannot conceptualize how to deal with the changing membership status and committee assignments.

Is this too complicated for a novice?

[June7]

Does sound complicated. Will need tables for committee assignments and chairmanships. Have fields for start and end dates.

[CJ_London]

basic setup is 4 tables - member, committee, assigned and attendance

As June7 says, you'll need start and end dates in the tables -something along these lines

tblMembers
MemberPK autonumber
MemberName text
isMember Boolean (true=member, false=non-member)
DateJoined date
DateLeft date

tblCommittees
CommitteePK autonumber
CommitteeName text
DateStarted date
DateEnded date

tblAssigned
AssignedPK long
CommitteeFK long
MemberFK long
AssignedFrom date
AssignedTo date

tblAttendance
AttendancePK autonumber
CommitteeFK long
isChairman boolean
MemberFK long
MeetingDate date


A basic algorithm you will need to learn is determining whether a date is between two dates for the purposes of managing date input

for example, to maintain data integrity when completing tblassigned, you would want to ensure you only assigned a committee that started on or before the assignedfrom date and the assignedto is less than or equal that committees deateended. Similarly the member cannot be assigned if the assignedfrom is less than the datejoined etc

Similarly meeting dates in tblattendance need to be between the tblcommittees datestarted and dateended and the member is in tblassigned against that committee and the meetingdate is between assignedfrom and assignedto

Alternatively, do not maintain data integrity as above, but instead use check queries that check this after the fact - because sometimes in the real world, the application of the data integrity rules makes the system unworkable or difficult to use in the heat of the moment (e.g. at registration) - better to mark someone as attending, even if they shouldn't be according to the rules, and sort it out later.

[JL512]

I do not believe it’s necessary for DateStarted and DateEnded fields in the tbleCommittees because all committees (except one) have been part of the organization since its inception. For the newer committee, I am not concerned (for now) about assigning dates or meetings for a committee that did not exist at a particular point. Or am I misinterpreting the purpose of these fields?

Will this basic structure allow me to track a member’s activity over time? Consider this possible member record with a summary of participation across 5 committees:

MemberID: #6

Committee	Active	Inactive	AssignedFrom	AssignedTo
Cmt#1	X	--	7/1/14	[current]
Cmt#2	--	X	--	--
Cmt#3	--	X	4/1/12	9/30/13
Cmt#4	X	--	2/1/15	[current]
	--	X	10/1/10	8/31/14
Cmt#5	--	X	--	--
	--	X	1/1/13	10/31/13
	--	X	1/1/09	6/30/12

For committees #1 and #2 it’s straightforward. He currently belongs to #1 and has never belonged to #2.

For committee #3, the member was a assigned in the past but not now.

Then it gets more complicated…

For committee #4, the member is currently assigned and was also assigned for a defined period in the past.

For committee #5, the member is not currently assigned but was assigned for two defined periods in the past.

Would an append query allow me to update a member’s assignment records?

[June7]

An UPDATE action is used to edit records. An INSERT action would create a new record.

However, those are not needed if data entry is accomplished with bound form.

[JL512]

So I can use the UPDATE action, as explained here:

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Or, use a bound form? I'm uncertain how to program that way. Should I continue with the self education or is the UPDATE method equally reliable?

[June7]

I prefer bound forms whenever possible. However, I do have situations where I use action queries. Since you are new, I recommend less VBA route of bound forms.

Why would you not continue with education regardless?

[JL512]

"less VBA route" - I won't argue with that advice!

And, yes, I do need to continue with the education. I was weighing (without actually knowing the facts) the difficulty of using the Insert Into command instead of a bound form.

I appreciate the help here and will keep drafting the DB design.