Using multiple genre categories in db, queries & reports

[DecaturLady]

I am a beginner with a little bit of experience. Am attempting to catalog my movie library. Movies come in all sort of genres and I wanted to note this information for retrieval. So far I have about 25 genre types (Drama, Comedy, History, Science Fiction, etc.). I thought the Yes/No data type was the best way to go. So my Data Entry Form has all the genres listed with a Yes/No check box.

However, queries are not performing as expected.

Ultimately, when I print a report, I want to see only the genre types attributed to the film. I'm now stuck. What suggestions might you have as to how I should structure the data and queries? Thanks for your help!!

DecaturLady

[orange]

What are your business facts/rules:

1 movie can be assigned 1 or many Genres

Sounds like you may have at least 3 tables

tblMovie -->MoviesHasBeenAssignedGenre<---tblGenres

[DecaturLady]

Sorry, this beginner doesn't understand. Would Y/N be in the TblGenres? All movies have genres, at least one. So why (or what is purpose of) do I need MoviesHasBeenAssignedGenre?
pam

[June7]

Appears your data structure is not normalized. Multiple y/n fields for same type of data is an indicator.

Need to learn relational database principles and Access functionality. Start with this tutorial site: http://www.rogersaccesslibrary.com/

What you have is a many-to-many relationship between 2 entities - Movies and Genres. This requires a junction table to associate a movie with its relevant genres. Each movie/genre pair will be a record in MoviesHasBeenAssignedGenre.

MoviesHasBeenAssignedGenre (or a shorter name tblMovieGenres)
MovieID (foreign key)
GenreID (foreign key)

No y/n fields.

[ssanfu]

There is OTS software to manage your collection.

Here are two links to free programs:
http://www.datacrow.net/#!prettyPhoto
http://www.emdb.eu/downloads.html


If you are developing a library to help learn Access, ignore the above.