creating a table from another table

[1959Nel]

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?

 

[Rick B]

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.

 

[Jerry Whittle]

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.

 

[Beardman]

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.

 

[Douglas J Steele]

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.

 

[1959Nel]

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.