Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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?
0
BBlu
Asked:
BBlu
3 Solutions
 
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
 
Jack LeachProprietorCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )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
 
clarkscottCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now