Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by 1959Nel, Jul 28, 2006.

Tags:
  1. 1959Nel

    1959Nel
    Expand Collapse
    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!!!
     
  2. Loading...


  3. Graham R Seach

    Graham R Seach
    Expand Collapse
    Guest

    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" <1959Nel@discussions.microsoft.com> wrote in message
    news:4CF8A407-3D4A-4146-9388-AD1A3AD5698E@microsoft.com...
    >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!!!
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    1959Nel wrote:
    > 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.


    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.

    --
     
  5. George Walsh

    George Walsh
    Expand Collapse
    Guest

    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.


    "1959Nel" <1959Nel@discussions.microsoft.com> wrote in message
    news:4CF8A407-3D4A-4146-9388-AD1A3AD5698E@microsoft.com...
    >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!!!
     

Share This Page