Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Creating a form with multiple records from tables

    I need some serious help with my database, i have created a database for my dvd collection and the relevant parts of it looks like this;

    Film
    Filmid Pk
    Director Fk

    Director
    Director-id PK
    First name
    Last name
    Born
    Death

    Genre
    Genre-id PK
    Name

    Moviegenre
    Genre-id PK
    Film-id PK

    Okay, so i have linked relation one to many between Film and Director,
    and a relation between genre - moviegenre - film (many to many)

    So my question is basically how i can type in all this information from the three tables Genre, director and film, in one form???

    Director and film is no problem, but when it comes to the last one it is always a problem, it cant be updated or the different genres get registered multiple times in the genre table..

    Hope that someone has something smart to say, I have just started with databases so I hope i get some help

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I would change your Film table to

    Film
    Filmid Pk
    Director Fk
    Genre FK

    On your input form have a text box for the name of the film and "Drop Down" boxes to display the Director and Genre, then store the relevant FK for the selected Director and Genre.

  3. #3
    Join Date
    Apr 2009
    Posts
    5

    Unhappy database reply

    Hi, thanks for your answer. But I don't quite get it.. By laying genre as an FK in Film, what happens to the tables Genre and moviegenre?
    Is there any possibilty in having three tables in one form?

    One of the things that makes it hard for me to have genre in the film-table is that there are many genres per film.. This is why i thought a table for the genre would be a good idea, but I have no clue about this

    I have problem picturing the last thing you said about; "then store the relevant FK for the selected Director and Genre"

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    What do you mean by "there are many genres per film"? Also what is moviegenre? Please give examples of each of the above and an example of an entry on the input screen.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Many genres per film is pretty obvious isn't it?

    One film can be classified into many genres. A movie can be both a horror and a comedy for example.

    moviegenre would be the intersection table between the many to many relationship between Movie and Genre.
    how i can type in all this information from the three tables Genre, director and film, in one form???
    You can't and you can. You would need multiple forms - one mainform and at least one subform for your movie genres. In the end you will be able to see/manage the details for a DVD, including all the items you described.... on one SCREEN, but not one form.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2009
    Posts
    5

    reply database

    Hi thanks for the answers.. Startrekker has right on the genrequestion... I start with the formwizard, then add movietable, director table and the genre table.... Thats were the problems start.. when I try to add information in the subform for genre, it would not let me.. so what do I do ? For example, I can add the movie Forrest Gump in the Film - table; year, name, length, publisher etc.. Then I can add Director information like; name, born, death etc... And then the problems start with the subform for Genre; all I want to have is the different genres per movie info in the form.. So I have tried adding both name and genreid, doesn't work and all I get is different error messages like; the info can't update etc... Any ideas?? Thanks for the answers poppa and startrekker

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You stated "For example, I can add the movie Forrest Gump in the Film - table; year, name, length, publisher etc.. Then I can add Director information like; name, born, death etc... And then the problems start with the subform for Genre; all I want to have is the different genres per movie info in the form."

    My question is WHY do you need to add the Director details as above for each entry? If you enter details for FILM_1 with the details entered for Director1, then you enter details for FILM_2 directed by Director_1 are you not duplicating the data for Director_1?

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Attached is sample database that may help you. You will need to make changes as per requirements. I have also created a Relationship diagram see Relationship from the toolbar menu.

    Open the form frm_film and you will notice that there is a subform to enter the various film's genres.

    For a new record you will enter the Film NAME, then select a DIRECTOR from the dropdown list, then add the GENRES in the subform. The subform is a continuous form.

    You will need an input screen to record the Director details and also an input screen to enter the type of Genres.
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2009
    Posts
    5

    re database

    Hi! Thanks alot, I see the problem with duplication, but what are my alternatives? Because in your example database I have to insert director info and genre info separately, right? This means that I have a whole lot of work to do and basically doubled up the work with over 500 dvds to fill in... This is basically a big problem and the reason I want all info on one form without filling in info in different places..

    I get the genreform, because this is not all that much work to enter new info about, but the director? How can I put in the other info about the director, and must I always type in info about the director before go to the form?
    What do you mean by an input screen and how do I get it? (Ain't that used to the english version of access)
    Sorry if I seem like a question mark on this..

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please attach a copy of your database so that I may offer a solution.

  11. #11
    Join Date
    Apr 2009
    Posts
    5

    re database

    Hi poppa, Can't manage to send it to you on this forum so im sending it with my mail, magsolum@mail.com

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Attached is an updated version of the supplied database. You will need to change the Field Captions from English to Norwegian.
    Attached Files Attached Files

Posting Permissions

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