How do i create a table from part of another table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table(Master) with students name and class (let say 5a, 5b, 5c). I
want to create a table(5a) for students of class 5a only. However, when i
update the master table, this should reflect on the 5a table. The 5a table
will have other fields that are not in the master table.

I can run a make-table query but that does not solve the problem of update
and keeping the data in the other fields.

Help deeply appreciated. I am stuck on this for 2 weeks now!!!
 
You would be better to have two tables; tblStudents, and tblStudentClasses.
The former would store information about the students, and the latter would
store information about the classes in which each student is enrolled.

tblStudent
StudentID (Autonumber) Primary Key
Surname (Text)
blah, blah, blah

tblStudentClasses
ClassesID (Autonumber) Primary Key
StudentID (Long Integer)
ClassID (Long Integer)
EnrolledDate (DateTime)

tblClass
ClassID (Autonumber) Primary Key
ClassName (Text)
ClassNo (whatever is required)

The relationships would be as follows:
tblStudent.StudentID --> tblStudentClasses.StudentID (One to many)
tblClass.ClassID --> tblStudentClasses.ClassID (One to many)

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
1959Nel said:
I have a table(Master) with students name and class (let say 5a, 5b, 5c). I
want to create a table(5a) for students of class 5a only. However, when i
update the master table, this should reflect on the 5a table. The 5a table
will have other fields that are not in the master table.

I can run a make-table query but that does not solve the problem of update
and keeping the data in the other fields.
Click to expand...

Guessing data types and constraints:

CREATE TABLE Master (
student_name VARCHAR(35) NOT NULL,
CONSTRAINT student_name__not_zero_length
CHECK (LEN(student_name) > 0),
class_code CHAR(2) NOT NULL,
CONSTRAINT class_code__pattern
CHECK (class_code LIKE '[1-9][A-H]'),
CONSTRAINT pk__master
PRIMARY KEY (class_code, student_name)
)
;
CREATE TABLE Class5a (
student_name VARCHAR(35) NOT NULL,
class_code CHAR(2) NOT NULL,
CONSTRAINT class5a_class_code__values
CHECK (class_code = '5a'),
CONSTRAINT pk_class5a
PRIMARY KEY (class_code, student_name),
CONSTRAINT fk__class5a__master
FOREIGN KEY (class_code, student_name)
REFERENCES Master (class_code, student_name)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;

The key points here are the check constraint
(class5a_class_code__values) which ensure only rows in the Master table
with values '5a' are allowed into the table and the foreign key
(fk__class5a__master) which ensures the row exsits in the Master table
in the first place.

Jamie.

--
 
1959Nel said:
I have a table(Master) with students name and class (let say 5a, 5b, 5c). I
want to create a table(5a) for students of class 5a only. However, when i
update the master table, this should reflect on the 5a table. The 5a table
will have other fields that are not in the master table.

I can run a make-table query but that does not solve the problem of update
and keeping the data in the other fields.
Click to expand...

Guessing data types and constraints:

CREATE TABLE Master (
student_name VARCHAR(35) NOT NULL,
CONSTRAINT student_name__not_zero_length
CHECK (LEN(student_name) > 0),
class_code CHAR(2) NOT NULL,
CONSTRAINT class_code__pattern
CHECK (class_code LIKE '[1-9][A-H]'),
CONSTRAINT pk__master
PRIMARY KEY (class_code, student_name)
)
;
CREATE TABLE Class5a (
student_name VARCHAR(35) NOT NULL,
class_code CHAR(2) NOT NULL,
CONSTRAINT class5a_class_code__values
CHECK (class_code = '5a'),
CONSTRAINT pk_class5a
PRIMARY KEY (class_code, student_name),
CONSTRAINT fk__class5a__master
FOREIGN KEY (class_code, student_name)
REFERENCES Master (class_code, student_name)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;

The key points here are the check constraint
(class5a_class_code__values) which ensure only rows in the Master table
with values '5a' are allowed into the table and the foreign key
(fk__class5a__master) which ensures the row exsits in the Master table
in the first place.

Jamie.

--
 
The Master Table you now maintain could be for Student information and in
the table structure an autonumber field would automatically be assigned as
each new student entered. This record ID number would become the unique
primary key that identifies each particular student.

A second table could be created with the class information that relates to
each student and a field in the second table would include a reference the
master table's primary key field in order to link each class table's record
to the proper student's record on the master table. Also, another field on
the second table would contain the student's class identifier (i.e., 5a,
5b, or 5c, etc.).

The two tables would than need to have a relationship identified as either
"One-To-Many" (assuming it is possible that each student could belong to
more than one class at sometime in his or her life) or a "One-To-One" (if
each student would only ever belong to one class). This specifies whether
for each Master table unique primary key (i.e., an individual student),
there may be Multiple records on the second Table (i.e., classes) or not.

Next, create a query using both tables and select the records you need to
display on a form from both tables. Indicate that the records should be
alphabetized by student names, and you will be able to filter the class
identifier field by selecting to display only the 5a, 5b, or 5c under the
criteria for the class identifier field. You can leave the query unfiltered
and allow the user to set the filter in the forms you create in the next
step.

Create a form populated by the query recordsource and display the student
information on this main form, then create a sub-form that is also populated
from the same query recordsource and use it to present the class
information. The sub-form will display within the main form to present the
class detail about each specific student.

You can set the record filter to 5a for example - using the query by form
method, and thereby only display the students that are in selected class.
Change the filter to other classes as needed. Also, any edits made via the
form will be maintained in the appropriate table.
 
The Master Table you now maintain could be for Student information and in
the table structure an autonumber field would automatically be assigned as
each new student entered. This record ID number would become the unique
primary key that identifies each particular student.

A second table could be created with the class information that relates to
each student and a field in the second table would include a reference the
master table's primary key field in order to link each class table's record
to the proper student's record on the master table. Also, another field on
the second table would contain the student's class identifier (i.e., 5a,
5b, or 5c, etc.).

The two tables would than need to have a relationship identified as either
"One-To-Many" (assuming it is possible that each student could belong to
more than one class at sometime in his or her life) or a "One-To-One" (if
each student would only ever belong to one class). This specifies whether
for each Master table unique primary key (i.e., an individual student),
there may be Multiple records on the second Table (i.e., classes) or not.

Next, create a query using both tables and select the records you need to
display on a form from both tables. Indicate that the records should be
alphabetized by student names, and you will be able to filter the class
identifier field by selecting to display only the 5a, 5b, or 5c under the
criteria for the class identifier field. You can leave the query unfiltered
and allow the user to set the filter in the forms you create in the next
step.

Create a form populated by the query recordsource and display the student
information on this main form, then create a sub-form that is also populated
from the same query recordsource and use it to present the class
information. The sub-form will display within the main form to present the
class detail about each specific student.

You can set the record filter to 5a for example - using the query by form
method, and thereby only display the students that are in selected class.
Change the filter to other classes as needed. Also, any edits made via the
form will be maintained in the appropriate table.
 
Back
Top