Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Unanswered: Relationship Problem

    Hi i'm new to Access and Forums, i have a big problem ... i have 10 tables, and i can't seem to get the correct relationships, even though they're all 1-to-many; if i enter data in the tables it just throws up error messages saying "you can not add or change a record, because a related record is required in table **** "

    INSTRUCTOR
    Instructor Name (pk)
    Courses an instructor may teach
    Courses an instructor specialises in

    CLIENT
    client name
    client employer
    client address
    client phone number
    client ID(pk)

    MODULE
    Module Name
    Module ID(pk)
    Comments

    COURSE
    Course Name
    Course Reference Number (pk)
    Course Duration
    Instructor Allocated to course

    VENUE
    Name of Venue
    Venue Address
    Date of Venue
    Venue ID (pk)

    BOOKING
    Booking ID (pk)
    Accommodation
    Meals
    Equipment


    i think i need foreign keys in the following tables but im completely unsure

    COURSE/INSTRUCTOR
    Course Reference Number (pk)
    Instructor

    COURSE/CLIENT
    Client ID (pk)
    Client Name
    Course Reference Number

    COURSE/MODULE
    Module ID (pk)
    Module Name
    Course Reference Number

    COURSE/BOOKING
    Booking ID (pk)
    Accommodation
    Course Reference Number

    I think i'm on my own here haven't got a clue how to sort it out

    Joe
    Last edited by blink182_joel; 04-21-07 at 08:53.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, good luck, because yes, you probably do need some foreign keys

    access does create primary and foreign keys for you, it does this whenever you build a relationship in the relationship diagram

    before building the relationships, though, it would be a good idea to know ahead of time which columns will be your PKs and which ones will be FKs

    and we aren't going to tell you that, for two reasons: 1) we don't do homework assignments on this forum, and 2) we don't know your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Rudy is quite correct ... However, your error message has to do with the fact of which table and key your related the other table and key to ... You have to pay very close attention to which direction you relate tables to each other in. Exanmple that shows this:

    Table A is related to Table B on PK C.
    You attempt to insert row into table B with PK of C.
    Access pukes because in order to insert into Table B, there HAS TO BE a corresponding record of PK C in Table A before the Table B record can be inserted ...
    This is your error.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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