Please help me organise my DVDs !

[Matt-79]

Hi everyone,

I'm a bit of an Access beginner, so hopefully these questions are very simple to answer. Thank you in advance for trying to help....

I'm trying to setup a simple database to organise my DVD collection and help me find movies that I may want to watch based on their genre and certificate.

I've started this by creating a single table (DVDs) which contains:
Movie Title (text field)
7 Genres: Action, Comedy, Horror etc (Yes/No fields)
Rating: (text field)

( There is a second table which lists the ratings [ U, PG, 12, 15, 18 ] for use in a combo box in a form )

My AIM is to have a form through which I can ADD new movies and SEARCH existing ones. So my form has the Title, Rating combo-box and 7 check-boxes listing each of the Genres. The first part (adding) I think I've sussed - enter a title, select a rating and tick as many check-boxes as you want (so one movie could have multiple genres, eg. Romantic Comedy).

It's the searching that's doing my head in.

What I'd like is to be able to tick one or more check-boxes to search for, hit a SEARCH button and generate a list of matching movies. Eg. Tick Romance and Comedy and receive a list of movies that have both Comedy and Romance ticked (plus any other genres it may have, eg. Drama).

However here's my problem: If I create a simple OR based query (where the Criteria of each genre check-box is on it's own OR line) I receive a list of ALL movies (not filtered at all). I think this must be because the check-boxes are either Yes or No. So if a genre check-box in my form is un-ticked this matches TRUE for a movie that doesn't have that genre checked in my DVDs table (ie. No = No = TRUE, Criteria met, list movie). Since every movie naturally has at least one genre not checked, this produces ALL movies when the OR Query is run. No good.

So I tried an AND Query (Criteria of each genre all on the same Criteria line). But this only lists films where the movies contain Comedy ONLY or Comedy AND Romance ONLY... not "anything including Comedy" or "anything containing Comedy AND Romance". Ie... this AND Query is too restrictive.

I then made a small break through in my OR Query. I found that if I made the Criteria read as follows:

[Forms]![Main Form]![Action-CheckBox] And -1

That this works for singularly selected genres. -1 seems to be the Yes of a Yes/No Query. So this Criteria is saying "If the check box in the form is ticked AND the genre in the DVDs table is Yes - then list the film". (Fixing the No = No = True problem).

However, if I select more than 1 genre in the form I receive a list of all movies containing, for example, Comedy OR Romance AND any others (Not Comedy AND Romance AND any others)... it lists all movies that include Comedy and all movies that include Romance. So very nearly there, but I can't figure out how to make it just right.

If anyone can help I'd really appreciate it. Moving forward I am also struggling with getting the Rating incorporated properly. But let's deal with one thing at a time

Thank you again for reading (sorry if I rambled on a bit there!!)
Matt

 

[WayPay]

This may be not at all what you're looking for, but unless you really want to "roll your own", there's a template database in Office Online . No idea what's in there, but it might be worth checking out..

 

[Matt-79]

Hi WayPay,

Thank you, but I did already see that Template and as massively complicated and impressive as it is... it doesn't appear to actually do what I'm after... filter movies out by category

 

[WayPay]

Had a look at your original post . First off, you may want to read up on database normalization. Those 7 genres should be in a separate table. The way you're going, you should use Excel (unless you're going to have more than around 65535 titles).

Rambling seems to be contagious..

that

[Forms]![Main Form]![Action-CheckBox] And -1

is the same as

[Forms]![Main Form]![Action-CheckBox]

-1 is, indeed, the value of Yes and True.

So this Criteria is saying "If the check box in the form is ticked AND the genre in the DVDs table is Yes - then list the film".

No, it's saying "If the check box in the form is ticked AND True". Unless there's some subtle bending of Boolean logic going on, that is .

I had a similar problem a few threads ago. If you post a sample database, I'll have a look at it (probably not until tomorrow, though).

 

[Matt-79]

Thanks again WayPay.

I'll have a read up on normalization and see how I get on. If I don't get anywhere I'll post what I've got so far

Thank you again.

 

[Singh400]

I would start with:

tblMovies
tblGenres
tblRatings

Then fill in 10-20 movies, and start experimenting.

 

[WayPay]

Happy to help. Yell when you get to the "now I really want check boxes in my search form" bit .

Take Singh's previous advice. Normalization might seem like overkill for such a small system, but it will pay off as soon as you add or remove a category. Besides, people will be happier to help you on future problems when they see you've done your homework .

Also consider: can a movie be in more than one category?

Choices, choices..