Form Design

[J. Renee]

I am putting together a database that will track enrollment into educational
series. The participants come from a table of all our clients, only some of
whom will enroll in education.

Each series runs for a course of 6 dates. Within each series, participants
can choose a specific class (a topic taught by a certain teacher).

Here are my tables:

tblFamily (list of all our clients - many fields but only the FamilyID is
relevant)
FamilyID - autonumber - PK

tblSeriesName
SeriesNameID - autonumber - PK
SeriesName

tblClassDates
ClassDates - Date/Time - PK
SeriesNameID - FK

tblClass
ClassID - autonumber - PK
SeriesNameID - FK
Topic
Teacher

tblEnrollment
EnrollmentID - autonumber - PK
ClassID - FK
ParticipantID - FK - FamilyID above

tblAttendance
AttendanceID - autonumber - PK
EnrollmentID - FK
ClassDates - FK
Present - Yes/No - to track if a participant attends their class each class
date

I have it set up with a form based on the Series Name with subforms to
assign Class Dates and Classes (topic/teacher) to each Series.

Another form is based on the Class with a subform to assign Participants to
each Class.

However, I can not figure out how to design a form to populate tblAttendance
and track whether each participant is present on each Class Date. Please
help with designing the Attendance form or even offer suggestions for better
table designs.

Thank you!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...4cc52e6ac449&dg=microsoft.public.access.forms

 

[Barry A&P]

you posted your question as a suggestion i dont know if that will limit your
replies much.

here is a nice little tutorial on youtube about updating attendance with an
apped query.

create a form called UpdateAttendance based on your tblClass with a subform
based on tblEnrollment add a Present Y/N field to tblEnrollment then create a
query Similar to this

INSERT INTO TblAttendance ( ParticipantID, AttendanceDate, enrollmentID )
SELECT Family.FamilyID, date() AS Expr1,
[Forms]![UpdateAttendance]![yourformsEnrollmentControl] AS Expr3
FROM TblFamily
WHERE ((([Forms]![updateAttendance]![yourformsEnrollmentControl]) Is Not
Null) AND ((TblEnrollment.Present)=Yes));

Please verify this code before use as append queries will add data to your
database. Be sure to backup before experimenting

then make a update button on your form to run the query.

once you make sure it works you can suppress warnings with VBA code

Others may have better info..
Barry