How do I create an attendance register for those present on a date

[smithypn]

I am trying to create a simple register of client attendance for a weekly
meeting (same time and day every week). There is a data table of possible
attendees for this meeting (say 50) but maybe only 20 will attend for each
meeting.
I want my database to:
1. Record who attended each weekly meeting, i.e. the attendance table should
have fields:
a. Date of Meeting
b. Active Clients (taken from the client table for each meeting but listing
only the fields :first name, family name and gender)
c. If present?
2. Add/remove/amend the client data table.
3. Analyse through queries to provide information on client attendance and
the nature of specific groups attending (data on clients held in table).

It is getting the first part to work that I am having trouble with.

 

[George]

Dear,

First you need the following tables:

1) tbl_Clients (will include all information about each customer) - Is
prefereble to have a unique value of each separate client, e.g. an ID, which
will be primary key.

2) tbl_Meetings - Since all meetings will take place in the same place and
at the same time you will need just one field, e.g. Meeting date, which must
be primary key.

3) tbl_MeetingParticipants, will include the meeting date and the client ID,
which must be both primary keys.

So, for each meeting you will have many participants (the same participant
will be allowed to enter once for each meeting.

Relationships: tbl_Clents one to many to tbl_MeetingParticipants,

tbl_MeetingDate one to many tbl_MeetingParticipants.

You may use a form to reccord meetings and participants, and then, using
some queries you may find for each meeting who where the participants and who
not and also (if youu have such data in your client table) nature of specific
groups attending.

Hope this helps,

GeorgeCY

Ο χÏήστης "smithypn" έγγÏαψε:

 

[Ken Sheridan]

To take things a little further, as you want to be able to show who was not
present at each meeting as well as those present you'll also need a way of
determining a client's eligibility for attendance at each meeting. The
simplest way would be to include DateJoined and DateLeft columns in the
clients table. You can then return a list of all eligible clients who
attended/were absent from each meeting with a query such as:

SELECT MeetingDate, Firstname, LastName,
IIF(EXISTS
(SELECT *
FROM tbl_MeetingParticipants
WHERE tbl_MeetingParticipants.ClientID = tbl_Clients.ClientID
AND tbl_MeetingParticipants.MeetingDate = tbl_Meetings.MeetingDate),
"Attended","Absent") AS Attendance
FROM tbl_Clients, tbl_Meetings
WHERE MeetingDate >= DateJoined
AND (MeetingDate < DATEADD("d",1,DateLeft)
OR DateLeft IS NULL)
ORDER BY MeetingDate, LastName, Firstname;

For each meeting the query will return rows for those members where the
meeting date is within their date joined/date left range, or after their date
joined date if they are current members and date left is Null. The subquery
tests for the existence of a row in the meeting participants table for each
client ID/meeting date combination from the outer query, and by means of the
IIF function returns 'Attended' or 'Absent' accordingly.

Ken Sheridan
Stafford, England