Tables in Access

[Guest]

I have a table for all teachers including their email

I have another for all students including a column to enter thier teachers
name and email, these two tables have a relationship that includes all
students and the names of the teachers where the names match.

In the student table I have a list box to assure the teachers names are
correct.

When I select a teachers name, how do I make access also include the
teachers email automatically

 

[Guest]

Don't attempt to save the email as a field in the student's table. You only
need to save the primary key of the Teachers table as a foreign key. To
display a field corresponding to the selected TeacherID, include the field in
the RowSource of the list box, and use the list box' Column property.

Set the following properties (change table and field names as appropriate)

RowSource: SELECT Teachers.ID, Teachers.Name, Teachers.email FROM Teachers
ORDER BY Teachers.Name;
Bound Column: 1
ColumnWidths: 0"; x"; 0", where x is wide enough to display the widest name

Since the key field column width is set to 0", the name will display in your
list box after the selection is made. For the email, add an unbound textbox
with the ControlSource equal to:

=YourListBox.Column(2)

Hope that helps.
Sprinks

 

[Guest]

I was able to input the row source information in the students table, but I
am still unclear how to add an unbound text box in a table if you told me not
to save the email as a field in the student's table.

Were do I add the unbound textbox with the ControlSourcw equal to:
=yourlistbox.colum(2)?

 

[Guest]

This issue gets at a common misunderstanding between Fields and Controls.
Fields are attributes of a table, and have a data type--Text, Number,
Date/Time, etc. Form and report controls DO NOT have a datatype, they are
merely a container in which to display data. If they are additionally Bound
to a field in the form's RecordSource, then data entered into the control is
additionally saved to the table field.

A control can have NO ControlSource, it can be bound to a field, OR it can
be set to the result of an expression. In the latter case, the control will
display the result of the expression, if it can be evaluated. This was what
I was suggesting, that you set the ControlSource of an unbound textbox to the
result of the expression that uses the list box' Column property. The full
syntax is:

YourListBoxName.Column(x), where x is the column number, starting with 0.

So the email address is to be displayed on your form, providing the user
information, but not stored to the Students table.

It also touches on the topic of Database Normalization. In a relational
database, each "thing" has its own table, and each "attribute" of the "thing"
is a different field. Each table should have a primary key, the value of
which uniquely identifies each record.

Since the email address is an attribute of the teacher, not the student, it
belongs in the Teachers table. All that is necessary to relate this student
to that teacher is to store the primary key of the teacher in a field of the
same type in the Student table. If you use an AutoNumber primary key in
Teachers, then it would be an Integer field type in the corresponding field
in Students. This is thus called a foreign key, and it "unlocks" or gives
"Access" to all of the field values of this teacher record via a query.

To add the textbox, open your form in Design View. Verify that the Toolbox
is displayed (View, and select Toolbox if it is not already selected). The
textbox icon is the one with the small "ab" followed by a vertical line.
Select, pick a spot on your form for its upper left hand corner, and then
drag to the bottom right corner. Use the Align and Size commands on the
Format menu to resize and position the control relative to the others. Then
set the ControlSource to the expression in my last post. Be sure that
"YourListBox" is changed to the Name of the list box control (the Name
property).

Hope that helps. For more information on database normalization, see the
following resources. In my opinion, it's the most fundamental topic in
developing relational applications. If your tables are not normalized, you
will be in for a host of problems.

http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172
http://support.microsoft.com/?id=129519
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Sprinks