It's a database about actors and movies. I want fill it with their age too, so I can answer the queries:

  • at what age they made their last movie.
  • what is the average age when they made their movies?

etc. These are my proposed tables: Actor table with the fields:

  • name
  • age
  • name of films made
  • year of films made

a Film table with the fields:

  • name of films made
  • year of films made
  • names of actors in that film

What would be the best one to use as key?

C. Then I'd probably need a linking table, with the fields:

  • Actors
  • Films

For all 3 tables: What would be the best field to use as a key?

my follow up question is: What other tables would I need?

And Access 2003 is the only one the client has a license for. Labor of love, volunteer stuff.

  • Your criticism seems iffy, considering the fact that Mokubai answered all my questions pretty accurately. And precisely. But, I'm open minded, so: how do you think I should make the question less broad? I'm really asking, I hope you can help me. Please be specific where I should be more precise. Bottom line: How do you think that I can improve upon the question and its subquestions? If you can't think of ways how I can do that, I'm asking you kindly to take this question on hold. Please don't hold the past against me. That's prejudice. Thanks! – GwenKillerby Sep 27 '18 at 21:43
  • of course I meant OFF hold. please remove the on hold thing. – GwenKillerby Sep 28 '18 at 17:39

To me those would be three distinct tables.

  1. Actors:
    • actorTableKey
    • actor name
    • actor date of birth (age can be calculated dynamically by subtracting DOB from current date)
    • actor biography
    • no film listings here
  2. Films
    • filmTableKey
    • film name
    • film description
    • film release date
    • no actor listings here
  3. The Linking Table
    • actorTableKey
    • filmTableKey

The Linking Table simply lists actorTableKeys and their related filmTableKeys.

Then you can do a groupby actorTableKey in the Linking Table to get a list of filmIDs that correspond to each actor, and conversely groupby filmTableKey to get actors by film.

My Access is a little rusty so I'm not sure of the exact query, but to me that is the cleanest way to do it without having to update the actor table every time you add a film or vice versa.

This is a many-to-many relationship using what access calls a junction table.

  • 1
    @ Mokubai: I am happy with this answer, it is pretty adequate, but naturally I hope that someone, who is more current on Access 2003, can improve on it, where he/she expressed uncertainty. – GwenKillerby Sep 27 '18 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.