Attendance

[bhrosey via AccessMonster.com]

I am working on a membership attendance report and I need to be able to show
each week whether or not a certain member of a family was present. There may
a case where dad and kids were there but mom wasn't, for example and I want
to be able to pull up a report of everyone that was absent. I want to be
able to simply use either a calendar or check box on my form. Not sure of
how to do this easily. Someone else will be entering this data on a weekly
basis.

 

[Maarkr]

I can't believe you want to track all that info... you must have a small
congregation. One idea that I've used but is advanced is a list box of all
family members on a form with a date picker combo and a Command button. You
would highlight all names in the list box that is present, then select the
date and click the button which dumps all names and dates into a junction
table. You'll need a table like Members, AttDates, and a junction table with
a link to Members ID and AttDates ID. You populate the members table with
info on each person to track, and the AttDates table could include info like
Message, Notices, and of course a date.

 

[bhrosey via AccessMonster.com]

thanks for the advice, I'll have to try something like that. I am not very
advanced, but I have some resources that I can use.

I can't believe you want to track all that info... you must have a small
congregation. One idea that I've used but is advanced is a list box of all
family members on a form with a date picker combo and a Command button. You
would highlight all names in the list box that is present, then select the
date and click the button which dumps all names and dates into a junction
table. You'll need a table like Members, AttDates, and a junction table with
a link to Members ID and AttDates ID. You populate the members table with
info on each person to track, and the AttDates table could include info like
Message, Notices, and of course a date.

I am working on a membership attendance report and I need to be able to show
each week whether or not a certain member of a family was present. There may

[quoted text clipped - 3 lines]

how to do this easily. Someone else will be entering this data on a weekly
basis.

 

[Michael Gramelspacher]

I am working on a membership attendance report and I need to be able to show
each week whether or not a certain member of a family was present. There may
a case where dad and kids were there but mom wasn't, for example and I want
to be able to pull up a report of everyone that was absent. I want to be
able to simply use either a calendar or check box on my form. Not sure of
how to do this easily. Someone else will be entering this data on a weekly
basis.

You might make a Calendar table with one column named CalendarDate.
This table has a date for each Sunday, assuming this is for Sunday church attendance.

Say you have a table Members with MemberID, FirstName,Lastname,BirthDate, etc.

Say you have a table Events with EventName. Main Service, Sunday school, etc.

Say you have an Attendance table like this:

CREATE TABLE Attendance (
MemberID LONG NOT NULL
REFERENCES Members (MemberID),
CalendarDate DATETIME NOT NULL
REFERENCES Calendar (CalendarDate),
EventName VARCHAR(50) NOT NULL
REFERENCES Events (EventName),
AttendanceStatus BIT DEFAULT 0 NOT NULL,
PRIMARY KEY (MemberID, CalendarDate, EventName)
);

This exa,ple query fills the table, but do it using a form.

INSERT INTO Attendance
(MemberID,
CalendarDate,
EventName)
SELECT Members.MemberID,
Calendar.CalendatDate,
Events.EventName
FROM Members,
Calendar,
Events
WHERE (((Calendar.CalendatDate) =#4/12/2009#)
AND ((Events.EventName) = 'Main Service'))
AND NOT EXISTS (SELECT *
FROM Attendance AS a
WHERE a.MemberID = Members.MemberID
AND a.CalendarDate = #4/12/2009#
AND a.EventName = 'Main Service');

Have a main form withe two combo boxes, one based on Calendar
and one based on Events. Also have a command button
with this code:

Private Sub cmd_CreateRoster_Click()

Dim strSQL As String

If Not (IsNull(Me.cbo_CalendarDate) Or IsNull(Me.cbo_EventName)) Then

strSQL = "INSERT INTO Attendance(MemberID,CalendarDate,EventName)" _
& " SELECT Members.MemberID,Calendar.CalendarDate,Events.EventName" _
& " FROM Members,Calendar,Events" _
& " WHERE (((Calendar.CalendarDate) =#" & Me.cbo_CalendarDate & "#)" _
& " AND ((Events.EventName) = '" & Me.cbo_EventName & "'))" _
& " AND NOT EXISTS (SELECT * FROM Attendance AS a" _
& " WHERE a.MemberID = Members.MemberID" _
& " AND a.CalendarDate = #" & Me.cbo_CalendarDate & "#" _
& " AND a.EventName = '" & Me.cbo_EventName & "');"

Debug.Print strSQL ' for testing only

CurrentDB.Execute strSQL, dbFailOnError
End If

Have a subform based based on Atendance joined with Members and linked to main form
Link Child Fields: CalendarDate;EventName
Link Master Fields: cbo_CalendarDate;cbo_EventName
End Sub

This should get you started.