Problems tracking attendance

[AKiss]

Ok, I'm at my wits ends here. I have tried to figure this problem out for over a week and it's driving me crazy. I've even tried to search here for information on this issue and can't seem to find anything closely related enough to the problem I'm having. Here is my situation:

I have built a database for a club. I need to be able to track attendance to each month's meetings for each member. I was not able to incorporate the attendance tracking, so I started a small database separate from the original just to figure out this portion (so as to not totally screw up all my other stuff that *is* working). Once I am able to make it work in the small 'test' database, I will be able to incorporate it into the real club database. So, to avoid listing all my information from my original database, here is the information from my test database:

3 tables: tblMembers, tblAttendance, tblMeetings

tblMembers: MemberID, FirstName, LastName
tblAttendance: MemberID, MeetingID
tblMeetings: MeetingID, Month, Date, Where, Type

The Primary Key for tblMembers is MemberID. The Primary Key for tblAttendance is MemberID & MeetingID. The Primary Key for tblMeetings is MeetingID.

There is a 1 to Many relationship between tblMembers and tblAttendance using MemberID. There is a 1 to Many relationship between tblAttendance and tblMeetings using MeetingID.

Now, for my actual question/problem. I'm pretty sure I need to put a field somewhere for marking a member present (probably a yes/no field). But, in which table would this field belong? I've tried all the tables and can't get it to work right with any of them.

Maybe I'm not on the right track with thinking there has to be a field to mark for being present. Basically, what I'm wanting to do is make a form with the Meeting details, then have a button to open a form that queries all members. Once that form is open, I want to be able to check off who was at the meeting and have those members show up on my Meeting details form so that a query or report can be generated later showing who all was present for each month's meeting and also to be able to count how many meetings each member has attended. I am able to create the queries and counts once I figure out the attendance part, but I just need help with how to set this all up. Maybe my relationships aren't right??

Any advice, suggestions, or examples would be greatly appreciated.

Thanks,
Anna

 

[jfischer]

1) Add an AttendedYn Boolean field to tblAttendance.

2) Set up an Append Query to add records to tblAttendance for each record in tblMembers based on a given meeting from tblMeetings. (You might want to put a command button on the main form to run this query to refresh tblAttendance in case new members have been added since the query was first run for a given meeting.)

3) The tblAttendance table then becomes the Record Source for a subform on a main form which has tblMeetings as its Record Source. The linked field between the two forms is MeetingId.

4) You can then check/uncheck the AttendedYn field on the subform to indicate the member's attendance at the meeting.

 

[AKiss]

jfischer,

Perfect!!! Works great!! Thank you so much. Instead of creating a command button to run the query to refresh the data, I put the code to do that in the 'On Open' function of the form. I also added the code to turn off the warnings so it was all automated (I was sure to add the code to turn the warnings back on at the end).

Once again, thank you so very much. I don't know why I was not able to look outside the box on that one.

- Anna

 

[LittleSmudge]

No, NO, NO

You have a classic Many to Many relationship between Members and Meetings

And you have a perfectly functioning 'Bridge' table between then called tblAttendance

You have ALL the fields you need. You do not need to go adding AttendYN fields


If MemberId = 47 attend MeetingId = 12 then in the tblAttendance you add a record containing 47, 12

If MemberId = 47 was AWOL from MeetingId = 12 then no record is added

Attendance of a member at a meeting is simply identified by the fact that a record exists !

You then get a list of all the meetings a member attended by
strSQL = "SELECT tblMeeting.* " _
"FROM tblMeeting INNER JOIN tblAttendance " _
"ON tblMeeting.MeetingId = tblAttendance.AttendanceId " _
"WHERE tblAttendance.MemberId = " & intMemberNo



And you can get a list of all the members who attended a meeting by
strSQL = "SELECT tblMember.* " _
"FROM tblMember INNER JOIN tblAttendance " _
"ON tblMember.MemberId = tblAttendance.AttendanceId " _
"WHERE tblAttendance.MeetingId = " & intMeetingNo



'ope-that-'elps


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[AKiss]

LittleSmudge,

But, then how would I check off who has attended each meeting? I understand that I had a Many-to-Many relationship. I just couldn't figure out where to put a check box to check off all the members that attended each meeting.

- Anna

 

[lameid]

The way I'd handle adding a member is to put all members in a listbox. In a second listbox put the attendance.

On the double click event of the the member listbox, append the record to attendance. On the double click event of the attendance listbox, delete the record. In each case you need to requery the attendance listbox. You don't have to use doubleclick... You could add command buttons. Or you could use and unbound checkbox and show all members and then on the afterupdate event run the appropriate code (This gets real sticky real fast if you want to autopopulate the checkboxes whith existing data).

The other thing I'd add is that you might want to have an enrollment table that has a start and end date for reporting purposes...

If I'm a member from January 2003 through March 2003, and I move away and come back in January 2004, you might not want to report that I was absent from the April 2003 through December 2003 meetings. Just a thought. If you have the data you can get at these things. If you don't and someone asks later...

 

[LittleSmudge]

You asked then how would I check off who has attended each meeting?

and I'd say - exactly as I put in my original reply


And you can get a list of all the members who attended a meeting by
strSQL = "SELECT tblMember.* " _
"FROM tblMember INNER JOIN tblAttendance " _
"ON tblMember.MemberId = tblAttendance.AttendanceId " _
"WHERE tblAttendance.MeetingId = " & intMeetingNo




You could then cycle through the recordset and pop the into a list box if you wanted or just concatenate then into a string with Chr13 Chr10 ( vbCrLf ) between each one and show that in a text box control.


'ope-that-elps


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[jfischer]

LittleSmudge,

AKiss's question is more of a user interface design issue than a database design issue. Your solution, which is technically correct for historical (after-the-data-is-built) queries, is of no help to a user trying to visualize on a screen who to mark present or absent. How do you propose to present the user with the information they need to see so that they can "add a record" for someone in attendance, or "delete" a record if they make a mistake?

 

[danvlas]

I totally agree with LittleSmudge.
And the solution with the Append query is a waste of space and resources...not to mention that it ignores that Access is a relational database.

As for displaying all members with their status 'attended/not attended', here's what I'd do:

Since all members should attend all meetings, create a query to display all combinations between them:

Query1:
Select tblMembers.*, tblMeetings.* From tblMembers, tblMeetings;

Then create a new one:
Select Query1.*, IIf(IsNull(tblAttendance.AttendanceID), False, True) As Attended From Query1 Left Join tblAttendance On Query1.MemberID = tblAttendance.MemberID And Query1.MeetingID = tblAttendance.MeetingID;

This will display exactly what you need. However, it is not an updatable query, so you will have to run some code if you want to insert a record in the tblAttendance table...

The final query can be used in any form or report and it includes all necessary fields.

Check thread702-501911 for a similar problem.
For a two-tables example, ftp://ftp.artrom.ro/Jethro.mdb (this one is even updatable)

Good luck


Daniel Vlas
Systems Consultant