Auto fill student details into other tables without retyping (1 Viewer)

Local time
Today, 05:50
Joined
May 24, 2022
Messages
1
(I am relatively new to ms access so this problem might have been solved for a different user, if so please direct me that post.) I am creating a database for my local school to :
1. Keep data of Students, their parents and etc.
2. Track school fees payments,
3. Track books distributions
4. payroll for the staff
etc

I wonder if there is a way to just type the name and class (basic details) of the students in only the details table and it will auto generate the same data in the school fees and other tables without having to retype the same data in the school fees table by creating a form to fill the student details
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2013
Messages
16,555
shouldn't be doing that anyway, database rules are (with a few exceptions not relevant here) to store data only once. Use a combo, listbox or subform to display the student data.

edit: sounds like you have not got to grips with database normalisation yet. It is not a difficult concept to understand. Google 'database normalisation' to find out more
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:50
Joined
May 7, 2009
Messages
19,175
you do not need to auto generate those info.
try also googling, Primary key and Foreign Key (on the joining tables).
in this way you can just Join your tables in a Query.
 

GPGeorge

Grover Park George
Local time
Today, 05:50
Joined
Nov 25, 2004
Messages
1,776
(I am relatively new to ms access so this problem might have been solved for a different user, if so please direct me that post.) I am creating a database for my local school to :
1. Keep data of Students, their parents and etc.
2. Track school fees payments,
3. Track books distributions
4. payroll for the staff
etc

I wonder if there is a way to just type the name and class (basic details) of the students in only the details table and it will auto generate the same data in the school fees and other tables without having to retype the same data in the school fees table by creating a form to fill the student details
Actually, you've had the answer a couple of times already, but in your question, you bolded one of the main reasons Relational Databases were invented in the first place. "...without having to retype the same data in the [related] table..."

That concept is at the very heart of designing and building tables in a relational database application. You already know WHY you need to work this way, but now you must learn HOW to do it in the properly designed set of relational database application tables.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:50
Joined
Feb 19, 2002
Messages
42,981
Welcome to AWF:) Your database has a lot of moving parts. Start with one section at a time. Start by defining what you want out of the application and then work back to what data you need to collect to get to the outputs. Layout the data in properly normalized tables (we can help you do that). Once the tables look solid, then you can work on creating queries, forms, and reports.

Here's a concept to think about, Students, teachers, and parents are all people. Depending on what kind of school you are running, teachers and parents might also be students. This is especially true for high school or college levels. So, it is perfectly reasonable to use a single table to manage all people. It adds a little complexity since you will need to define roles to help you to filter the people for certain types of pick lists. But it reduces complexity because it reduces the forms/queries/reports needed to manage "people" so you'll probably come out ahead by using one maintenance process and if you are going to do this, you need to do it from the beginning. Otherwise, it adds so much rework that it isn't rational to combine the three types of people after the fact.

As someone mentioned earlier, data is stored in ONE place but can be referenced from any related place by using what we call a foreign key (FK). An example follows.
tblPeople
PersonID (autonumber, PK)
LastName
FirstName
FamilyHeadID (FK to tblPeople) This points to the record whose address you want to use for the family
...

tblClass
ClassID (autonumber, PK)
ClassName
ClassYear
Semester
Teacher (FK to tblPeople)
...

tblClassRoster
ClassRosterID (autonumber, PK)
ClassID (FK to tblClass)
PersonID (FK to tblPeople)
FinalGrade

If you want to create a class roster you join tblClass to tblPeople and select the ClassID. Then you can select information about the class as well as information about the students. You don't need to repeat the information in tblClassRoster. The FK's let you pull it in when you need it.
 

Users who are viewing this thread

Top Bottom