Solved

Memberships and Attendance

Posted on 2014-02-10
7
187 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 85 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, Everyone!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

11 Experts available now in Live!

Get 1:1 Help Now