If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can Information Auto-Populate?
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. |
#2
|
|||
|
|||
Can Information Auto-Populate?
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" wrote in message ... 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. |
#3
|
|||
|
|||
Can Information Auto-Populate?
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" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
Can Information Auto-Populate?
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 "Mocahantas" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|