Multiple Genre Parameter Query in Movie Database

[Spuggyleeds]

Hello,

Little bit of background to my problem...

I have over 800 films which I've converted from DVD to iTunes format. I have added them to iTunes and extracted the data to Excel. From Excel, I have imported the data into MS Access.

So, now I have a movie database of my films. See attached fields for all tables and the Relationships.

As you can see, I have created a separate Genre table as this will be my main search parameters.

I can run a simple query which returns the search results for example 'Comedy'. My problem is, I am wanting to do a search for films with for example 'Comedy' and 'Drama'. Whichever way I try this, I don't get the right answer. I either seem to get duplicate records or no results returned.

I attach the query I am running which works for 1 Genre only!

I'm not sure if I have things set up correctly as I have to manually type the Genres into the Genre table which links to the films. I've been tinkering with this for weeks and searching everywhere for a solution (there must be one out there) and it's driving me mad.

I am not sure whether I have some fundamental problems with it and it's better to start from scratch?! I have the raw data from Excel which I could use to start again.

Please help!

 

[pr2-eugin]

I hate to break it to you Jason, but your Tables are not in the right structure. I would suggest you to have a look at http://www.databaseanswers.org/data_models/library_of_film_tapes/index.htm and http://stackoverflow.com/questions/490464/how-to-design-a-movie-database

Make a small change to the design and the information you wish to retrieve would be simple. With this structure the data retrieval could be a bit tricky.

 

[Spuggyleeds]

Thank you for your response pr2-eugin

I have looked at the links and am still a little confused.

Could I just point out that the 'Genre Type 1-7' in the tblFilm table is just for reference which came across in the Excel import and isn't actually used for anything as it's just a simple text field.

The tblDirector table is empty at the moment as this will be used later if I manage to get the multiple queries working.

I have been manually typing the genres into the tblGenreType which is linked to the tblFilm which I think is right?!

Is there something simple I can do without having to start again? I could remove the 'Genre Type 1-7' from tblFilm, but am not sure what starting again will do. I'm sorry if I'm being too simplistic with my database. Is there a book or something you could recommend that will help me or an online resource as I don't want to keep bothering you with my questions if I can help it?

Fingers crossed you can help.

 

[Spuggyleeds]

Anyone able to help me at all?

I think for you guys this must be beyond simple

 

[Spuggyleeds]

Sorry I haven't been on here for a while. Am still struggling. Any help would be very much appreciated!