Hi everyone,

I started in access just a copple of weeks ago, to automise my ecell
DVD database.So I could make forms, ans reports.. Thought it would be
easy, but I'm trying nog for weeks and I think I have a bad base to
start from.

First, I made 3 tables:
- Acteurs: acteur ID + acteur
- Genres: genre ID + genre
- Films; film ID + film titel + genre ID + acteur ID1 + acteur ID2 +
acteur ID3 + acteur ID4 + memo

Relationships:
- acteurs: acteur ID -> film: acteur ID1
- acteurs: acteur ID -> film: acteur ID2
- acteurs: acteur ID -> film: acteur ID2
- acteurs: acteur ID -> film: acteur ID3
- genres: genre ID -> film: genre ID

The generall idea I had was:
- to add multiple actors to one movie
- to made it easy to print off all the movies of 1 actor.
- to make a form for easy input, with comboboxes for the actors.
- to make a query for simple looking up the movies of 1 actor/genre.

Encountered problems for weeks now:
- By making relationships for the actors, access makes duplicate
tables, for so far, no problem.But how can i display the name of the
actor instead of an actors ID number, without using te "loopUp Wizard"?
- Is there a Query needed?
- .. i'm stuck here..

Thanks for the help anyway.

I'll give you a start

Do not limit yourself to 4 actors in a film. Remove all the references to
Actors from the Film Table
Create another table called something like JoinFilmActor with FilmID and
ActorID. If you highlight both of them you can create a joint key.

You must then set up the relationship to enforce referential integrity.

Your Main form will have the FilmTitle, notes Group and ID
It will also have a continuous subform with as many actors as you want You
must set the LinkmasterFields and LinkChildFields to FilmID.

Just 1 more tip. Do not have spaces in your table names field names or
anything else for that matter. It saves a lot of typing later on. as you
will have to put brackets round everything and that gets confusing. For
example Actors!ActorID is much easier than [Actor]![Actor ID]

HTH

Phil

"CryingFreeman" <brechtgijbels (AT) hotmail (DOT) com> wrote

CryingFreeman wrote:

Why not create a 4th table...film actors? When you enter a film, you
can select a genre from the lookup table of genre via a combo. Enter
some notes into the memo. In a subform, enter the actors in the film.
This table would contain an autonumber (FilmActorID), the ActorID, and
the FilmID.

Hi Phil,

First of all, thank you for your reply. I really do appreciate it when
people want to help someone out...
- also thanks for the tips with the spaces, I really will keep it in
mind with my rebuild of my new database.

I sad at the bar yesterday evening and there was a programmer who tried
to help me out. He said to try this:

Film: ID - titel - year - memo
Persons: ID - name - last name - date of birth - gendre
Rolls: ID - actor - dresser - producer - ...
FPR: FID - PID - RID - sequence

He told me it was much easier like this, while each movie has its own
FPR (number). So there could be an unlimited offer of actors to one
movie, even when actors are also producing or others things in the same
movie.

What do think about his idea Phil? Your opinion matters to me, so I can
see if you think this is better than your own suggestion.

Thanks!

Hi Phil,

Here I am again, bugging you with my problems . I just tried out
your sollution, and it works fine! It was just te thing I was looking
for, for weeks now. But I still remain with one question:
Now I can see with subforms in the table: Films wich actors are in it.
But how can I make the same suform in the table: Actors, to see in
which movie the actor played.. I'm struggeling it out. Thanks for your
help anyway!

Thanks Phil,
I found the solution to view which movies what actor played in. So I
can see it in my subform on table view. The only thing that is a little
anoying is when you open the table:actors, you first see the movieID
number, and than you have to hit the plussign again to view the titel..
But it works! And I'm Happy!
Thanks for all your help man! Really appreciate it!

OK a couple more things.

1 If you want to see the persons function in each film the structure
suggested is fine.

2 Tables should never be a source of information to humans. They should
always be inn the background. Much the same applies to queries. Absolutely
essential but should never be used.
Information should always be entered using forms - you can then (after you
learn a bit of Visual Basic) start to check that the information is
sensible. For example if you want to enter the date Of Birth of a film actor
and you enter a date before say 1820 a check would throw out an error.

Either forms or reports (you don't have to print them - just preview ten on
screen) are the ways to get information out of tour program In particular
you could create a report grouped by actors.

Phil

"CryingFreeman" <brechtgijbels (AT) hotmail (DOT) com> wrote