Project : Making a Video store management software
Softwares being used : Access 2007 (Database)
Visual Basic 6 (Interface)

Ok so for school I have to make a Video store management software. Basically there is a main table in the database which hold records of every single movie in the store. There are several fields where I am mainly focusing on 3 which are Genre 1, Genre 2, and Genre 3. Basically all movies have more than one genre example, Dark Knight is Action, Crime, and Thriller so for the dark Knight record I would give Genre 1 as Action, Genre 2 as Crime so on...
Now there are several other read only tables. These tables represent genres example: Comedy table, action table etc. and they are automatically updated by the main table.
Now I researched how to go on with this then I realized I had to do some SQL thing so I studied on w3schools website and made something like this for the Action table.

OK I got everything wrong the first time

OK Now what I want to do is from MainTable if Genre1, genre2, or genre3 is equal to Action then that whole record will be transferred to the ActionTable.
NOTE: The fields of both tables are exactly the same.

This post has been edited by Imran23: 28 Jun, 2009 - 03:11 AM

Is that database structure required by your school? You should rethink it, I think. When you find a need to specify 1 or many things of a particular type for something you should try to place it in a separate table, by saying Genre1, Genre2, Genre3 you limit yourself to only have three Genres per Movie, say you need 5 to actually present the Movie properly?

I would do something like this:


Now you can have None or how many genres you want for each and every movie. Now if you want to select all action-related movies you could do something like this:


Now I haven't tried this sql query and definitely not on Access, so it might not work, but I think you might get something out of it anyway! Hope it helps!

Well the db is not necessarily asked by the school. See I have to take this Computer exams from a International Examination Board where there is course work - 30% and Theory 70% so for the 30% Course work I need to create a computerized management system of my choice which will elliminate the current manual management with papers and all that. And also the new computerized system has to be much more efficient. So I made up a very complicated manual system where there are several physical drawers, each drawer representing a genre so a movie with multiple genre needs multiple records to be made to put in more than one drawer, Yeah I can't explain in brief but I have a whole word file with the manual system in details.