New to Access

[GarveyUSMC]

My Resources to use:
Access 2007 - For Dummies 9 in 1
Access 2007 - The Missing Manuel
The internet of course

I have read about 300 pages of Access for dummies. I have had C++ programing in HS but that was about 12 years ago. So it is beyound rusty. He is what I am looking to design right now. I figure if I can do this it will be a good push to be able to do a lot more things.

Let me think if you guys think this is possible to do please.

I have about 600 movies that are always growing. I want to be able to have a screen to pop up that allows me to Enter new movies, or another screen to look at all my movies I have and to be able to search them in many different ways.

I was thinking I could seperate this with 2 tables. They would look like this:

tblMovieList
MovieName
MovieYear
Plot
Actor1
Actor2
Actor3
Actor4
Actor5
Movie Img (possibly ole obj)?

tblGenre
MovieName (Bond to tblMovieList name)
Action Check Box
Adventure ""
Animation ""
about 20 more Genres

Alright those would be my two tables. Now I can put those on a form together and what happens is if you enter a new movie you have to put the name first hit save and then you can edit the genre.

I have done all the above and this is good and works for me. But now my end project I want a screen to come up with:

SearchMovie (Would this be another form attached to a query?)
"Search by Actor: " [text box for any actor they want it to search]
"Search by Year: " [text box for year]
"Search by Director: " [text box for director]

Below this I would like a check box for every Genre possible so if they chose to they could just click Horror and Comedy and a movie like Army of Darkness would show along with any other movies that fit that criterea.

Once this search is compiled what would happen. Would the results go to a Report page?

Is all of this possible? and if so any helper steps and suggestions. Here is my structure so far too.

Semper Fi

 

[pbaldy]

I haven't read those books, but the first thing you should do is read up in either one (or elsewhere) about "normalization". You don't want the repeating actor fields in the movie table, or the repeating genre fields. You want related tables where each actor/genre is a record.

 

[georgedwilkinson]

You need to look into normalization before you even start a design like this.

For example, your "MovieLists" table has a place for 5 actors. What happens if you decide there are 6 actors? 7? 200? Are you going to totally re-design all your tables, forms, and reports? A properly normalized design will not have that problem.

Same with "Genres". What if there is a new genre that comes up?

The action of searching a movie is just a built in function of Access. All you need to do is design the tables correctly and put them in the forms correctly and any dummy can filter the form to show the content they are interested in.

My rule of thumb is to create a table for every distinct thing in the system. An actor is a distinct thing, a movie is a distinct thing, a producer is a distinct thing, a genre is a distinct thing. Then look for similarities. An actor is a lot like a producer: they're both people or parties. Then look for relationships between all the different entities you've defined: relationships with be 1:1 (VERY rare and probably only used for polymorphism), 1:M, and M:M. Then resolve the M:M down to 2 1:M relationships by using a junction or associative "entity" (maybe a table later). Name the junction tables after the 2 tables they define the relationship for: TitleGenres; TitleActors; etc.

Once you have the table design correct, the stuff just kind of falls onto a form in an amazing way that is unique to Access (IMHO). There is really very little need for VBA or macros to make the application work correctly for its business purpose (note that security and advanced functionality are not part of the business purpose of a system).

Good luck. Yell back if you have SPECIFIC questions about your project.

 

[GarveyUSMC]

Thanks. Neither book talks about normalization. At least not under that word. I understand what you guys are talking about though and will think I saw something on this website about it so I will start there! Thanks gentlemen!

 

[georgedwilkinson]

Also take a look at Wikipedia's normalization articles (difficult read but worth it for the stuff you can understand) and tdan.com (the website for snobby data modelers/administrators).

 

[pbaldy]

If the books don't address normalization, they haven't helped you build the proper foundation under your application. You're doing the right thing, because you would have been working around that design for the duration of the project. Post back if you get stuck or want thoughts on a revised design.