Welcome to SPN

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

Sign Up Now!

Tables in Access

Discussion in 'Information Technology' started by the8thdove, Nov 4, 2005.

Tags:
  1. the8thdove

    the8thdove
    Expand Collapse
    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
    --
    the8thdove
     
  2. Loading...

    Similar Threads Forum Date
    India 11 Constables Get Pregnant at Training School Breaking News Apr 26, 2011
    Warm Up with Winter Vegetables (Vegetarian Casseroles) Cooking & Recipies Feb 7, 2011
    India Two constables arrested for beating, extortion Breaking News Jul 27, 2010
    History Cleaning Out the Augean Stables Punjab, Punjabi, Punjabiyat Aug 25, 2009
    Sikh News Cold weather damages sugarcane, vegetables in Punjab (New Kerala) Breaking News Feb 1, 2008

  3. Sprinks

    Sprinks
    Expand Collapse
    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

    "the8thdove" wrote:

    > 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
    > --
    > the8thdove
     
  4. the8thdove

    the8thdove
    Expand Collapse
    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)?
    --
    the8thdove


    "Sprinks" wrote:

    > 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
    >
    > "the8thdove" wrote:
    >
    > > 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
    > > --
    > > the8thdove
     
  5. Sprinks

    Sprinks
    Expand Collapse
    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

    "the8thdove" wrote:

    > 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)?
    > --
    > the8thdove
    >
    >
    > "Sprinks" wrote:
    >
    > > 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
    > >
    > > "the8thdove" wrote:
    > >
    > > > 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
    > > > --
    > > > the8thdove
     

Share This Page