Solved

Memberships and Attendance

Posted on 2014-02-10
7
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

710 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