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?

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.

The answer is "Forms" and "Combo Boxes"
You should avoid entering data directly in tables except for real quick and dirty personal apps

pootle flump ur codings are working excelent.

Here's a movie db I created a while back. You may find something useful in it.

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.

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 : )

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

Access 2007, Windows XP, 64-bit.

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.

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?

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.