Welcome to SPN

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

Sign Up Now!

creating a table from another table

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

Tags:
  1. 1959Nel

    1959Nel
    Expand Collapse
    Guest

    How do i populate a field (ex StudentName) of table student by records kept
    in another table( ex. Master)?

    The two tables are not identical but have a common field: StudentName

    Everytime the master table is updated, this should also be reflected in
    table student automatically?
     
  2. Loading...

    Similar Threads Forum Date
    Canada Ottawa weighs creating ambassador for religious freedom Breaking News Oct 4, 2011
    India SGPC Accused of Creating a Row Breaking News Jan 20, 2011
    Pacific International Gathering of Young Sikhs Focuses on Creating Good Citizens Breaking News Jan 8, 2011
    Are We Creating New Devtas? Sikh Sikhi Sikhism Aug 21, 2009
    Sikh News Sharif warns against creating instability in Punjab (Express India) Breaking News Sep 19, 2008

  3. Rick B

    Rick B
    Expand Collapse
    Guest

    You don't. You don't store the same piece of information in two places.

    Instead, you would only store the student NUMBER in the related tables.
    Their name would only be stored in one table.

    This is the whole point of a relational database.


    --
    Rick B



    "1959Nel" <1959Nel@discussions.microsoft.com> wrote in message
    news:7008F565-B165-4A6F-B06D-9191062707A9@microsoft.com...
    > How do i populate a field (ex StudentName) of table student by records
    > kept
    > in another table( ex. Master)?
    >
    > The two tables are not identical but have a common field: StudentName
    >
    > Everytime the master table is updated, this should also be reflected in
    > table student automatically?
     
  4. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    The pedantic answer is that you should not update one table based on
    information in another. Now there are exceptions to everything so we would
    need a better understanding of what you are doing and why.

    In this particular situation are you dealing with something like a student
    changing their name due to marriage or something similar? In that case the
    StudentName, as it can change, should not be used in a relationship between
    the two tables. Something like a StudentID number that will never change for
    the individual student, should be the link between tables.

    If it's a matter of two tables needing to have the same data, then that's a
    mistake as you are finding out. Something like the StudentName should be in
    one and only one table.

    I highly suggest getting some relational database training or reading
    "Database Design for Mere Mortals" by Hernandez before proceeding any further
    on this database.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "1959Nel" wrote:

    > How do i populate a field (ex StudentName) of table student by records kept
    > in another table( ex. Master)?
    >
    > The two tables are not identical but have a common field: StudentName
    >
    > Everytime the master table is updated, this should also be reflected in
    > table student automatically?
     
  5. Beardman

    Beardman
    Expand Collapse
    Guest

    How do I include related-table info in derivatives of main table?

    Sorry for the use of this thread. My "new questions"-mode doesn't respond.
    --
    A (main) table is used to gather lots of data, and also to create documents,
    based on templates in a different MS-application (MSWord) Its usage is to
    create addresses in merged mail, and include additional information from the
    main table.

    What if a list of say, names of cities, is in a related table, to appear as
    a lookup table in the main table.

    When creating a template from the main table, to create addresses, the city
    becomes a number.

    How do I include the name of the city in the main table, such that it will
    be available for external use?

    Thanks in advance.
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Re: How do I include related-table info in derivatives of main table?

    You need a query that joins the two tables and returns the appropriate
    columns from both. You then use that query rather than the table.

    It sounds as though you're using the Lookup field "feature" in Access. You
    might be interested in reading http://www.mvps.org/access/lookupfields.htm
    to see some reasons why many of us consider that to be a bad idea.

    You should never be working directly with the tables, so there's no reason
    to have a lookup field in a table. Put your own combo box onto the form, and
    populate the table that way.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Beardman" <Beardman@discussions.microsoft.com> wrote in message
    news:C6B66EA6-9678-49B8-8C38-371749875681@microsoft.com...
    > Sorry for the use of this thread. My "new questions"-mode doesn't respond.
    > --
    > A (main) table is used to gather lots of data, and also to create

    documents,
    > based on templates in a different MS-application (MSWord) Its usage is to
    > create addresses in merged mail, and include additional information from

    the
    > main table.
    >
    > What if a list of say, names of cities, is in a related table, to appear

    as
    > a lookup table in the main table.
    >
    > When creating a template from the main table, to create addresses, the

    city
    > becomes a number.
    >
    > How do I include the name of the city in the main table, such that it will
    > be available for external use?
    >
    > Thanks in advance.
     
  7. 1959Nel

    1959Nel
    Expand Collapse
    Guest

    Thanks Jerry for your insight. I read Access for dummies and thought i knew
    all!!

    Here is the scenario more explicity.

    In a school all students id, name and class are stored in a master table.
    Now somer students belong to say Englishdepartment based on their classes.
    The english department would like to add other details like book number,
    marks etc to the records of their student into their own table. What should
    happen according to me is that the Id field of the english department table
    should automatically be populated with the Id(if not the name) from the
    master table based on the class criteria.
    There are different departments and each one is adding their own details.
    How do i achieve this.
    Appreciate your help.
    "Jerry Whittle" wrote:

    > The pedantic answer is that you should not update one table based on
    > information in another. Now there are exceptions to everything so we would
    > need a better understanding of what you are doing and why.
    >
    > In this particular situation are you dealing with something like a student
    > changing their name due to marriage or something similar? In that case the
    > StudentName, as it can change, should not be used in a relationship between
    > the two tables. Something like a StudentID number that will never change for
    > the individual student, should be the link between tables.
    >
    > If it's a matter of two tables needing to have the same data, then that's a
    > mistake as you are finding out. Something like the StudentName should be in
    > one and only one table.
    >
    > I highly suggest getting some relational database training or reading
    > "Database Design for Mere Mortals" by Hernandez before proceeding any further
    > on this database.
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "1959Nel" wrote:
    >
    > > How do i populate a field (ex StudentName) of table student by records kept
    > > in another table( ex. Master)?
    > >
    > > The two tables are not identical but have a common field: StudentName
    > >
    > > Everytime the master table is updated, this should also be reflected in
    > > table student automatically?
     

Share This Page