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 3 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
3 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, 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.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.
