Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Location
    Albany (Colonie), NY
    Posts
    2

    Unanswered: How to Write a Select Statement in Access

    I can't figure out how to write a select statement other than a very simple one. I have a table called "Orders" and among the fields are "StudentID", "FirstName", "Initial", and "LastName". I have another table called "Students". This table has fields with the same names. I have been able to set it up so that when I enter the Student ID is checked to be sure that the ID is valid. (Same ID cannot be used twice.) What I want to be able to do is enter the Student ID in the "Orders" table and have the "FirstName", "Initial", and "LastName" automatically entered from the "Students" table. Could someone write the statement for me or give me guidance? Thank you for your assistance.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You're breaking one of the cardinal rules of database normalization.
    You don't need to store the same info in two different tables.
    If you already have student name info in the students table, then
    storing the studentID in the orders table is sufficient. Then
    whenever you need to know the student name, you use a query like

    SELECT FIRSTNAME, INITIAL, LASTNAME
    FROM STUDENTS INNER JOIN ORDERS ON STUDENTS.STUDENTID=ORDERS.STUDENTID

    Otherwise, you can end up with different names assigned to the same student id in the orders table.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Database design (incl normalisation):
    http://r937.com/relational.html

    SQL tutorial:
    http://www.w3schools.com/sql/default.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2007
    Location
    Albany (Colonie), NY
    Posts
    2

    How to Write a Select Statement

    Thank you for the input. Actually, I thought of doing that but thought using the select statement would be better. I now see the error of my ways. Have to forgive an old man who tries to learn these applications without going to school.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •