Updating a field in another table

  • Thread starter Thread starter Chip
  • Start date Start date
C

Chip

Yep, I am new to ACCESS but I am building a database that tracks college
students and basically have a Student table (tblStudentInfo) with the
students general information then another table with each semester
(tblStudentSemester) that each student attends the college. What I want to
do is that when a new entry is made into the tblStudentSemester then a field
in the StudentInfo table is updated to indicate the last year (and possibly
semester) the Student attended the college for quick reference on the
StudentInfo Form. Is this possible? VB is not a fluent language to me so
please be gentle!!
 
Chip,

According to the rules of "database normalization", you
should never store data that you can otherwise retrieve
through data already stored and available.

Instead of trying to update that field every time you make a
new entry, one approach would be to use a calculated control
to display the latest date in the semester table. You can do
this with no VBA either on the form or reporting level by
setting a control source to....

= DMax("WhateverYourDateFieldIs", "tblStudentSemester",
"[StudentID]=" & Me!StudentID)

Gary Miller
Sisters, OR

Chip said:
Yep, I am new to ACCESS but I am building a database that tracks college
students and basically have a Student table (tblStudentInfo) with the
students general information then another table with each semester
(tblStudentSemester) that each student attends the college. What I want to
do is that when a new entry is made into the
Click to expand...
tblStudentSemester then a field
 
It sounds as if you have a basic design issue. I assume that each student
has one tblStudentInfo record, and that each student has one or many
student semester records (tblStudentSemester), and the two are related by
the studentinfo primary key ID.

Thus, if you want to be able to see all the student's information and the
year of the last symester, build a form (or report) based on a query
containing all of the student info and probably "Max(aDate)" from
tblStudentInfo of the last simester record (formatted for year, or as you
wish).

As a matter of proper design, you don't want tables to contain redundant
information, and so should not have a field containing the most recent
year). The latest date of the student's enrollment is retained implicitly in
tblStudentSemester(something in the record has to indicate the semester)

John S
Aylmer, PQ (Canada)
 
Back
Top