Table/Relationship Indetermined

[Keli Alway]

Hi I am setting up a database to keep track of our training classes. I have
to track absences and count them for the week as well as a cummulative count.
When I am doing the cummulative count, it is counting all the absences for
their past classes and their current class. I think there is something wrong
with one of my tables.

Tbl_Associates
-Emp ID - Primary Key
-Last Name
-First Name

Tbl_Activity Info
-Course Title (there are 3 courses)
-Activity Code (Individual code for the particular class) - Primary Key
-Instructor Name
-Location
-Start Date
-End Date

Tbl_Activity Details
-Activity Code - Primary Key & One to Many relationship with Activity Info
-Emp ID -Primary Key & One to Many relationship with Associates
-PC#
-DN#
-Status (Complete, Incomplete, Drop)
-Withdrawl/Drop Reasons

Tbl_Attendance
-Emp ID - Primary Key - I feel like this should link to Tbl_Activity Details
but I get an Indetermined Relationship. I can link it to the Associates
table, but then when I go to count absences I can't count the total absences
on just this Activity, I get all activities they have been in.
-Date -Primary Key
-Week #
-Late
-Late From
-Absent
-Preplanned
-Time missed

Any ideas on how to fix this so I can group the attendance by the Activity
Code? I also have Weekly Progress reports that have to be done every week per
Activity and when I create my form, it get the reports for all activities,
not just the activity code I have them enter in the query. It's pretty much
the same problem, just different info no being able to group by Activity
Code. Please help!

 

[tina]

so Tbl_ActivityDetails has a combination primary key, made up of fields
ActivityCode and EmpID, correct? looks like this table is basically a class
roster, and you want to keep attendance records for each class, against the
roster. so add BOTH fields of the primary key to the attendance table, as
foreign key fields. so each attendance record will be linked to a specific
employee in a specific class.

hth