Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: Relational Database Problem

    Hi guys, I'm fairly new to databases and am just creating sample ones at the moment and following tutorials from books. I tried to create my own cinema/movie database yesterday and there's something wrong.

    I really wanted to work this one out for myself but after trying many different combinations of various relationships I just can't seem to get it working.

    I have four tables..

    A member table: MemberID
    A booking table: MovieID, Screening Number, MemberID
    A movie table: MovieID
    A screening table: Screening Number


    So the booking table is a composite key that links the member tbale (via the memberid), the movie table (via the movieID) and the screening table (via the screening number)

    The problem occurs when I try to make a booking form for members to book a movie.

    I use the form wizard and take the following fields:

    The MemberID from the member table, the Screening Number and MovieID from the booking table. Then I add other relevent information from the Movie and screening table (for example, screening time, date, price etc from the screenig table and movie name, information from the the Movie table)

    The MemberID is displayed on the main form with the other information in a sub form.

    The problem I am having is when I enter a booking on the form, I get an error telling me the Microsoft Jet Engine cannot find the screening number in the screening table or something like that.

    It lets me enter the other information but not with the screening number.

    Thanks for any help.

    Benn.

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    I may be naive by saying this but why are you using the form wizard and selecting the 'Member Table"? Asking because it seems like your booking table has all of the information you need in it so I think you should be using that one instead. That's probably why it doesn't find the screening number.

  3. #3
    Join Date
    Jan 2008
    Posts
    5
    Because the member will enter their ID and then make a booking. If I use the MemberID from the booking table then I cannot have the booking information as a subform.

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Have you tried to put the screening number in the Member table as a foreign key?

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    So, take it out of the screening table? Sorry, I don't know where I'd then put it in order to link them.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    No, you'd put it into the Member Table in design view and the relationship would show them together. Depending on what you have, it'll be a 1:1, 1:Many, or Many:Many relationship.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Shouldn't the movie and screening tables contain more than just an ID?
    With your current design; you have to be a member to make a booking, is this corect?
    George
    Home | Blog

Posting Permissions

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