Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2010
    Posts
    10

    Unanswered: Movie Info Database, Circular Reference?

    Hi everyone; I've taught myself Access over the past couple weeks to create a movie information database for my collection. Least to say I'm a bit "green" and need a hand with figuring this out:

    I've got three core tables that I need to relate:
    Titles
    Actors
    Roles

    I've gotten the database working with Titles and Actors, but adding Roles into the mix has put a wrench in my gears. This is my current setup:

    TITLES TABLE
    TitleID (primary key)
    Title

    ACTORS TABLE
    ActorID (primary key)
    Actor

    ACTOR-TITLE TABLE
    TitleID (primary key)
    ActorID (primary key)

    This works. I've been able to add "007" titles along with "Sean Connery" and "Roger Moore" actors and get it all to work out. But now, how do I create the roles? I imagine I would add the tables:

    ROLES TABLE
    RoleID (primary key)
    Role

    ROLE-TITLE TABLE
    RoleID (primary key)
    TitleID (primary key)

    ROLE-ACTOR TABLE
    RoleID (primary key)
    ActorID (primary key)

    I created these tables and relationships and Access did not stop me from making the circular references, but now how do I attach "James Bond" to "Sean Connery" and "Roger Moore" for the "007" titles? I imagine I'd have to create subdatasheets in the roles table that are based on some query that will return the actors for each movie? My head is spinning. When I had just the Titles and Actors tables, Access made the subdatasheet for me and attaching each actor to a titleID was easy. Now because each table has multiple relationships, when I click the expand subdatasheet button, I get a prompt asking me for master and child links. I don't know the first thing about setting this us properly. Or even if it can be set up properly considering the circular reference. Any ideas?

  2. #2
    Join Date
    Aug 2010
    Posts
    10
    Rather than dealing with the subdatasheets, I've been entering in the relationships via the linking tables (ie, Roles-Titles and Roles-Actors). It's working! I'm able to display roles corresponding to each character for a given title; so a circular relationship is not a problem for Access. I do, however, need to find a more friendly way of entering in the relationships than using the linking tables and selecting the right primary key numbers that correspond to the intended data pairs I'm linking. That is a different subject altogether and I'll look for suggestions elsewhere in the forum.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The answer is "Forms" and "Combo Boxes"
    You should avoid entering data directly in tables except for real quick and dirty personal apps
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's a movie db I created a while back. You may find something useful in it.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2010
    Posts
    10
    I've spent the past few days writing the macro that will pull the info from IMDb and populate the database. After getting it running and populating a few movies, I found a flaw in the design that I need some help with. Once again, I've got 6 tables:
    Titles
    Titles-Actors
    Actors
    Actors-Roles
    Roles
    Roles-Titles

    Any one actor may play multiple roles and be cast in multiple titles. Any role may be played by multiple actors in different titles. Your mind baked yet?

    Here's the goal: I want to create a form that shows the Title of the movie and a list of the actors in the movie along with their roles in it. I have constructed this as a form (containing the title) with a continuous subform in it (containing 2 columns, one with actor, one with role). Before I added ROLES to the mix, this form worked fine at populating actors related to a title. However, I tried adding a ROLES column in the actors subform and it just ends up displaying multiple instances of every actor along with every role that exists for them in the entire database. I imagine I'd have to make a query of roles for the query of actors? It's a bit complicated, so I attached a stripped-down version of the database to this message with some info in it.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2010
    Posts
    10
    PS, open the database, click forms, and open "Master List". The full version of the database does a lot more (pictures of the titles, lots more info), but all of that works fine so I stripped it down to just the malfunctioning bit : )

  7. #7
    Join Date
    May 2010
    Posts
    601
    I tried your database and I get a missing reference to:

    CompatUI 1.0 Type Library
    c:\windows\SysWOW64\compatui.dll


    What version of Access are you running?
    What version of Windows are you running?
    ** Include if 32-bit or 64 bit versions of the above
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Aug 2010
    Posts
    10
    Access 2007, Windows XP, 64-bit.

  9. #9
    Join Date
    Aug 2010
    Posts
    10
    I think it's a query issue. I know how to display a list of actors for a title, but how do I display a list of roles that correspond to those actors for only that title? A query inside a query? But that would likely just return one role per actor, so I'd have to create a separate form for each actor? Maybe the structure of the database is flawed to the point of not being capable of returning a list of roles given a list of actors and a title.

  10. #10
    Join Date
    Aug 2010
    Posts
    10
    It's ALIVE! Got it working correctly by adding the "Titles" table to the query; the TitleID from the Actor and TitleID from the Role are not connected without it. A new issue (not so much an issue as an annoyance):
    I add titles to the database with a vba script. In the VBA script, before adding an actor or role from a new title, it looks through each record in the database to see if they are already in there. This is done with DLOOKUP. Now that I have 30+ titles in the database (along with 1000+ actors), it lags like hell when adding a new title. I suspect the source of the lag is the DLOOKUP command. If I add a new title with 20 actors and there are already 1000 actors in the database, the code uses the DLOOKUP command 20000 times to compare each new actor to each already existing actor. Perhaps I could speed it up if I could read the entire table into an array, perform all the comparisons and additions on the records there, then dump it all back to the table. Any thoughts?

  11. #11
    Join Date
    May 2010
    Posts
    601
    Maybe a sub query would work better that the Dlookup.

    If you will post another sample of your database, I will be glad to see if I can assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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