How to count records from different fields?

[Lightningkid]

Hi, I have no prior experience to coding nor Ms. Access but a friend told me this is the best software for my assignment, which is I have to list 50 movies and their 3 main actors/actresses. In the end I have to count how many times a specific actor/actress play in those 50 movies.



I've made a table, consisting of Title, Actor/Actress (1), (2), and (3). Is there any way to make a query to compile all names from all 3 fields and count their titles?

I found this piece of code which counts how many a specific actor/actress play:

SELECT [Movies].[Actor/Actress], Count([Movies].Title) AS [Total Video]
FROM [Movies]
GROUP BY [Movies].[Actor/Actress];

but it only counts the actor/actress from first field, which is not sufficient enough...

[ranman256]

No, your table is structured wrong. You you want 3 tables: tMovies, tActors, and tMovieStars

tMovie table
---------
movieID ,Title,year,director
1,Die Hard
2,The 6th Sense



TActor table
-----
ActorID,FirstN, LastN, birth
99, Bruce , Willis,
5,Haley Joel Osmet

TMovieStars (the join table of movies and actors.
-----
MovieID,ActorID
1,99
2,99
2,5

This is a normalized structure. Now you can have infinite actors in infinite movies.
and count them.

[Lightningkid]

So basically I have to make another record of the same movie for second and third actor? shucks

[ssanfu]

Well, if you make a table for the movie names ("tblMovies") and enter all of the movies, then you have a table for Actors ("tblActors") and enter all of their names, you can use a junction table "tblMovieStars" with a main form/sub form to enter the movie/actor. This is quick because you have a combo box to select the movie (in the main form) and a combo box (in the sub form) to select 3 stars.

Creating a query to count how many times a specific actor/actress play in those 50 movies is easy.


Or you can create a table like you proposed and pull your hair out trying to count how many times a specific actor/actress play in those 50 movies.

Your choice....