How would I link these two?

[chellebell1689]

Hello, I've got two tables, Members & SS_Attendance. I am making a form for the SS_Attendance and it pulls the First Name, Last Name, SS_Roll, and SS_Class from the Members table. I tried making a check box to check if that member was there when we take roll. I tried leaving it unbound, but it checks/unchecks all boxes; I tried making it bound to the appropriate field in the SS_Attendance table (via expression), but it doesn't let me click on the box; I tried adding the existing field straight from the SS_Attendance table, but it asks how the two tables are linked. I really feel this is the solution (but could be wrong); how would I go about linking these two tables?

(Let me know if you need more info)

 

[GohDiamond]

If your tables look like this:

tbl_SS_Attendance

• SS_Roll
• OTHER_FIELD
• ANOTHER_FIELD
• YET_ANOTHER FIELD

tbl_Members

• First Name
• Last Name
• SS_Roll
• SS_Class

tbl_SS_Attendance.SS_Roll should probably have a relationship to tbl_Members.SS_Roll

That way when you select the item in tbl_SS_Attendance.SS_Roll all the MEMBERS associated with .SS_Roll will show up in your recordset

Are you building your main form with recordsource tbl_SS_Attendance
and adding a subform with recordsource tbl_Members?

If so then you use the master/child relationship when you add the subform
MASTER: tbl_SS_Attendance.SS_Roll
CHILD: tbl_Members.SS_Roll


Hope that helps point the way,
Cheers!
Goh

 

[chellebell1689]

Thank you.

I have my form based on my master table (pulling the first & last name, onRoll, and ss_class) then we hit a save button I created which runs an append query to update the ss_attendance table with their name, onRoll, four/five dates and four/five checks. (The checks and dates are based on each Sunday of the current month).

 

[GohDiamond]

Can you upload a screenshot of the form?
Goh

 

[chellebell1689]

hi, sorry it took me so long to respond. I only have access to the data when I work on it (which in mon - wed, for a few hours each day). Here's the screen shots you asked for. Also, I included a screen shot of the query that runs when the "save" button is hit.

 

[Cronk]

If there is trouble finding an obvious link between 2 relational tables, it is usually a sign that there is something wrong with your table design. This is the case for you.

Tables should represent an entity. In your case, one entity is a Member, the other is a Roll Call.

The appropriate table design would be
tblMember
MemberID Autonumber Primary Key
FirstName Text
Surname Text
(Any other relevant fields for the member eg birthdate, address, phone)

tblSSRollCalls
RollCallID Autonumber Primary Key
RollDate Date
SS_Class Text (or preferably index to a lookup table)

A third table is then required to record whether Members were in attendance.

tblAttendance
AttendanceID Autonumber Primary Key
MemberID Long
RollCallID Long
Attend Boolean
(any other fields related to the Roll call eg homework (Yes/No)

There is then some work to do to populate the tblAttendance table.

It's not a trivial exercise and you might find it preferable to use a spreadsheet.

 

[chellebell1689]

Thanks for that. The reason my attend table was set up so, is because I watched a youtube video (by Access Learning Zone) that showed how to do attendance the way I had it set up. The only difference is that my db has different classes.