Can Information Auto-Populate?

[Mocahantas]

I am creating a database to track student violations. One student may have
many violations so I have one table with the student data (their unique
identification number, their last name, first name, and residence hall). I
have another table with all the violation data (date of violation, type of
violation, sanction issued if any, etc.). Since a student may have more than
one violation, is there a way that the student's last name and first name can
auto-populate if I try to put in the same unique identification number? That
way, if it is entered correctly once, I won't have to worry about mispellings
of names.

Hope that made sense. Thanks.

 

[Jeff Boyce]

The easy way is to DON'T DO IT!

If your table of student data already has name/etc. info, use it! Create a
query that joins the violation data with its "parent" record from the
student table and use the student data you already have entered.

If you try "auto-populating" a second table with the same data, you are
wasting space, storing redundant data, and risking data integrity. (and the
purists will argue that you are violating relational design principles...).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mocahantas]

Thanks Jeff,

I'm still working on learning Access so I will try to wrap my brain around
how to create the query. Here's a follow-up question: In the violation
table, how would I go about notifying the user that the student is NOT
already in the system? For example, since I have the table with student data,
each student would only have to be entered one time. When I start to enter a
violation in the violation table and put in the student's identification
number, can Access tell me that the unique identification number is not tied
to a record in the student data table (so I would need to go to the student
data table and create their record)?

Or would that all be solved by the same query? On my way back to the Access
tutorials...

Thanks.

 

[Jeff Boyce]

If your two tables are related "one-to-many", you could use the
Relationships window to set this up, enforcing referential integrity. This
should prevent you from adding a violation for a student that doesn't exist.

On the other hand, if you are working directly in the tables, STOP NOW!
Access tables are great for storing data but are not intended for display,
add/edit, etc. That's what Access forms are for.

And if you use forms, you can use the standard "main form/subform"
construction. This helps you put your student information ("one" side) in
the main form, then see (and add/edit) all related violations records
("many").

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP