Solved

Memberships and Attendance

Posted on 2014-02-10
7
188 Views
Last Modified: 2014-02-23
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
Comment
Question by:BBlu
7 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39848602
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
 

Author Comment

by:BBlu
ID: 39848636
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
 
LVL 4

Accepted Solution

by:
Jack Leach earned 150 total points
ID: 39848653
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 85 total points
ID: 39848712
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
 

Author Comment

by:BBlu
ID: 39848839
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
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 80 total points
ID: 39849954
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
 

Author Closing Comment

by:BBlu
ID: 39880918
Thanks, Everyone!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now