Results 1 to 4 of 4
  1. #1
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243

    Assigning Relationships?

    How to link these 3 tables:


    Advisor table – one advisor has many students. [one to many]
    Student table – one student has one advisor [ one to one]
    Grant Number table -- ? [this would contain the StudentID assigned by the software and the grant number assigned by our department's internal tracking number.

    Both student and advisor share same campus/department.

    How do I link the tables so when I open the advisor table it shows the name[s] of each student it has been has been assigned to? Perhaps by the grant number table containing the student ID and the student grant number? Would the grant ID table be a junction table?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't see that the grant has anything to do with the advisor. The Student table should have a field for Advisor foreign key. Set Relationship link on the pk/fk Advisor ID fields.

    Are you working directly with tables and queries instead of forms?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Quote Originally Posted by June7 View Post
    I don't see that the grant has anything to do with the advisor. The Student table should have a field for Advisor foreign key. Set Relationship link on the pk/fk Advisor ID fields.

    Are you working directly with tables and queries instead of forms?
    Yes -- I'm setting up the tables before I tackle a query or form. Thanks for the feedback -- I'll try what you advised.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here are some free video tutorials that deal with RDBMS, database design , Normalization and ERD. You should watch these and get familiar with the concepts. Build an ERD, as the video shows, and understand why the relationships are what they are.

    BUild a data model to get your initial tables structured and related.
    Watch these free videos, that are available to anyone with access to the internet. There are other videos, these are just a sample.

    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

    You can go back and watch these as often as you want.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. assigning a variable
    By crowegreg in forum Programming
    Replies: 1
    Last Post: 08-22-2011, 05:55 PM
  2. Assigning value to an image
    By cdzajp in forum Forms
    Replies: 1
    Last Post: 06-16-2011, 12:05 PM
  3. Assigning an Automatic PO #
    By nag3397 in forum Access
    Replies: 1
    Last Post: 01-27-2011, 02:22 PM
  4. Ranking and assigning a Value
    By loopyl00 in forum Access
    Replies: 0
    Last Post: 01-11-2011, 12:14 PM
  5. assigning probability
    By TheShabz in forum Programming
    Replies: 1
    Last Post: 05-06-2010, 12:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums