Hi Richard, I have a movie database I am cataloguing all my old DVD's into and I have managed to build a list in Excel which I want to import into the database. The table that holds the information for the movies is just a standard table with 4 fields that are set up to look at another table and stores the index number for the value/s that match the selection. These tables are for Format, Genre, Actor/s, and Director/s. In the Format and Genre fields, I only select one choice from each of the tables. For the Actors and Director fields it can have one or many from each table matching to the one movie. I have investigated how this information was stored and discovered that Access has a hidden table that it sets up and keeps track of these relationships but I am at a loss to understand which table it creates and how it keeps track. I have around 700 DVD's and although I can import names, dates and numbers into the Movie Table, I cannot come up with a way to add entries for the Actors and Directors apart from manually adding them after I import the basic information into the Movie Table. Is there a way to import this information as there is over 600 Actors in the Actor Table and for each movie, I would have to scroll through this list and select each Actor. I am sure you can realise this is a lot of scrolling and will be very time consuming. Although I am retired, I do not want to spend this much time on manually entering the data as I would much prefer developing my social golf club database which you have helped to do in many ways. Please let me know if you need any more information or clarification. Much appreciated, live long and prosper John
Hi Matt,
Thank you for your response. I did go and look at the video you suggested and was aware of the many to many table.
My issue is with importing text into a table that has fields that record the index number from a selection made from a combo box which gets it's data from another table.
For example, when I enter a new movie into the database, I have a form with several text boxes as well as a combo box for the actors. I have a table that has a list of Actors. When selecting which actors to add, I get the list of actors with a check box next to each name. I can select one or multiple actors and save the record, Access remembers all the selections associated with that movie.
My problem is how can I import the list of actors associated with each movie.
I have tried a tab delimited text file with the number associated with each actor separated by a comma, but this has not worked.
I only wish I could send you examples an pictures to explain what I am talking about.
Cheers
John
Matt Hall
@Reply 9 months ago
My response was assuming that your current database was the Excel file. After re-reading, it sounds like you have created an Access table with lookup fields and are trying to import into that. If that is what you are using, Richard generally recommends against them. As a result, I haven't used them in an effort to ward off future pain.
If you are using lookup fields, you might be interested in Lookup Wizard andEvil Access Stuff before you get too far down the path.
Let me know if I missed the mark here.
John RobertsOP
@Reply
9 months ago
Hi Matt,
Thank you for your reply. The database I built was based on a downloaded template which was locked. I recreated it because I wanted to add some more features and fields.
I was not aware of Richard's views on the lookup fields but now that I am, I will be redesigning the database using Richard's Many-to-Many method.
This will allow me to manipulate the data in Excel and import it into the tables simply and easy.
Since finding Richard's video, I use them as a bible for all my access database development.
Once again you guys have nailed it and I thank you very much for your advice and time, GREAT JOB.
Live long and prosper,
John
Sorry I missed this one. I'm catching up on some older forum posts.
Matt's got it. Glad you found the Many-to-Many lesson and have a solid path forward. Rebuilding it that way will make importing and maintaining all those actors and directors much easier.
And yes, your social golf club database sounds like a much more fun place to spend your retirement time. Live long and prosper!
Kevin Yip
@Reply 10 days ago
I have a movie collection database built on Access, and my first tip is put all the people in one table instead of separate tables for actors and directors. That's because a person could be both an actor and director, even in the same film. If you put the same person in both the actor and director table, that would violate the normalization rule that mandates that each entity can only exist once in a database.
Regarding importing cast & crew into the database expeditiously, I copy an IMDb cast & crew page like this ( https://www.imdb.com/title/tt0092007/fullcredits/ ) and paste it into Excel, then run Excel VBA code to parse the HTML info into a table format, which is then transferred to my Access database. It's pretty much an automated, hands-off approach other than pressing Ctrl-A Ctrl-C to copy the page.
Btw, building a movie collection database in Access (and SQL Server) has been the most challenging database project I've ever done, more so than any database projects I did for work.
Excellent point, Kevin. A single PeopleT table is the better design. Then your MoviePersonT junction table can store the MovieID, PersonID, and Role - Actor, Director, Writer, etc. That also handles someone like Clint Eastwood, who can be both actor and director on the same movie.
And yes, movie databases can get surprisingly complicated fast. They look simple until you start dealing with multiple people, roles, characters, alternate titles, editions, and all the other fun stuff.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
