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?
BBluAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jack LeachConnect With a Mentor ProprietorCommented:
Tables aren't meant to be interacted with... the proper way is to use a Form, and a ComboBox control, with the RowSource set to a query that will return a list of names... something like:

SELECT ClientName FROM Clients;

This will show a dropdown, similar to a lookup field, with a list of available clients.  By looking around at other property settings for the ComboBox control, you can infer many ways to format and manage the data - for example, you can have two columns and bind the first column, while hiding it from view, which is common to show a description but store an ID.  You can also choose whether the bound value must exist in the list of entries and whether the user is allowed to edit those values.

Tables should be raw data only with no meaningful interface to view that data.  Instead, forms and reports should be used, with queries being the intermediary between the tables and the forms/reports.

hth
-jack
0
 
Jack LeachProprietorCommented:
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
0
 
BBluAuthor Commented:
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?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
T_ClassAttendance should store the following:

 - Student = T_ClientList.ID
 - Class Day/Time = T_ClassAttendance.ID

If you store it that way you won't have to worry with changing names, since you're storing the Autonumber ID value (a surrogate key).

When you need to see the Names of your Students:

SELECT TCA.*, TCT.* TC.First + ' ' + TC.Last AS FullName FROM T_ClassAttendance AS TCA INNER JOIN T_ClientList AS TCL ON TCA.Student=TCL.ID INNER JOIN T_ClassTimes TCT ON TCA.[Class Day\Time]=TCAT.ID

And please be careful of your naming. A field named " class date/time" does not adhere to any standard naming conventions. It should be something like "dtClass_Date_Time"
0
 
BBluAuthor Commented:
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.
0
 
clarkscottConnect With a Mentor Commented:
Create a query of your ID and NAME (sort the name ASC).
Use a combo box and use your query as its source.
In the combo box columns properties:
Set to 2 columns
In column widths = 0";2"
This will make the ID number vanish while showing only the name in the combo.
Still assigning the ID to the combo box bound field - but you won't see it.

Scott C
0
 
BBluAuthor Commented:
Thanks, Everyone!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.