Entering attendance dates

[KLP]

I created a database to, among other things, track member attendance. It has
the following tables:

Individual - contains info on each person
Attendance - auto ID, dates, present (Y/N field), individ ID (number field
linked to Individual table above).
Open dates - auto ID, Event date, attend id (number field linked to
attendance table above).

I would like to create a form where all the members are listed, and the
event date is entered, the event date and whether they attended that day is
then added to the attendance record for that person.

The form I have now is by individual. Using a combo box, I select the
member and their attendance record (on a sub form) appears with the dates
attended shown. This form is tedious because I have to select each member,
and enter the same date over a hundred times, besides, it makes the report of
absentees difficult since the report only shows those members who were
present at least one meeting.

Any ideas would be appreciated.

TY

Kelvin

 

[John Vinson]

I created a database to, among other things, track member attendance. It has
the following tables:

Individual - contains info on each person
Attendance - auto ID, dates, present (Y/N field), individ ID (number field
linked to Individual table above).
Open dates - auto ID, Event date, attend id (number field linked to
attendance table above).

I would like to create a form where all the members are listed, and the
event date is entered, the event date and whether they attended that day is
then added to the attendance record for that person.

The form I have now is by individual. Using a combo box, I select the
member and their attendance record (on a sub form) appears with the dates
attended shown. This form is tedious because I have to select each member,
and enter the same date over a hundred times, besides, it makes the report of
absentees difficult since the report only shows those members who were
present at least one meeting.

Use another form, turning this one inside out.

Base the mainform on the Open Dates table, with a combo that lets you
select an event. Base the Subform on a Query such as

SELECT Individual.IndividID, Attendance.IndividID, Present,
Individual.LastName & ", " & Individual.FirstName, <any other needed
fields>
FROM Individual LEFT JOIN Attendance ON Individual.IndividID =
Attendance.IndividID
ORDER BY IIF(IsNull(Attendance.IndividID), 1, 0), LastName, FirstName;

This combo will show all individuals, whether or not they attended;
the Attendance.IndividID will be NULL if they did not attend this
event. I'm sorting by the IIF expression to display all attendees
followed by all absentees. You could just put a checkbox in the
subform bound to Present to zip down the list and check all attendees.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[KLP]

Thanks John for the info.

I know what to do or where to put everything except the FROM statement.
Where is that entered?

Kelvin

 

[John Vinson]

Thanks John for the info.

I know what to do or where to put everything except the FROM statement.
Where is that entered?

Create the query first just in the query design window, and join the
two tables by dragging the fieldname. Then look at the SQL view of the
query.

John W. Vinson[MVP]