asked on

Memberships and Attendance

I am working on a database to manage my bootcamp. Currently, I'm setting it up so that I can track attendance. I have the following tables:

T_ClientList
- ID (unique, autonumber)
- first
- last
- contact info.

T_ClassTimes
- ID (unique, autonumber)
- class date/time
* I currently have 3 classes on most days, so I'll have 3 separate records for each

T_ClassAttendance
- ID (unique, autonumber)
- Student (lookup to T_ClientList)
- Class Day/Time (lookup to T_ClassTimes)

I use the lookup to find the students easily, then assign a class time. I'll copy down the class day and time for all of the students for that particular class.

It just dawned upon me, though, what if I have a student's name spelled wrong and change it later. What is the best way to set it up. I'm assuming I need to return two columns for the lookup, then store the ID, not the name. Is that correct?

Jack Leach

You should have a table for Students, and Referential Integrity (relationships) set up for the corresponding tables... set the relationships to Cascade on Updates, and if you change the student name in the main students table, it will propagate everywhere that Student is a Foreign Key in other tables.

Edit: I realize now that your T_ClientList is basically the Student's table. Ditch the Lookup Fields and enforce RI between the T_Clients and the other table, similar to my first take at an answer...

hth
-jack

BBlu

ASKER

Thanks, Jack. If I want to be able to "lookup" the name by the complete name, should I create a query first, then use that in the relationships, so I can see first and last together?

ASKER CERTIFIED SOLUTION

Jack Leach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

Thanks, Scott. Yes, I figured I should store the ID, but I need to see the names in order to know who is whom. I'l try to figure it out.

Jack,
Forms our currently outside of my expertise, but I'll try to figure it out.

SOLUTION

clarkscott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

Thanks, Everyone!