Access 2007 Tutorial 5.1. Many to Many Import

This video is the first of a series of three videos where we will be talking about how
you can use the Table Analyzer Wizard and complex queries to import multiple many to
many data sets into Microsoft Access. In this video, I am going to import data from
Microsoft Excel. Unlike my previous video, where I imported a simple dataset from the
Federal Elections Commission, this data will contain two many to many relationships. This
makes the import process a bit more complicated. If I take a look at my data in Microsoft Excel,
this data was created by my Access class, and in the class, students create their own
databases containing movies they have selected with the actors and directors that appear
in those movies. In order to create the later assignments in
the class, I take all of their data and combine it into one very large data set. When I look
at the data set, I have 342 rows of information in a flat file, really am Excel spreadsheet.
I have directors, movie titles, ratings, the release year, the actor last name, the actor
first name, and I have the name of the student who created the data. For the database, I
call the student the customer and say that they have rented the movie.
I will now open Microsoft Access. I want to create a blank database. I will call this
database “movies”, which I will save on my desktop. Access has opened to a default
table, which I will close so the table disappears. I will click on the external data ribbon and
import the Excel spreadsheet, clicking on browse, going to my desktop, finding my spreadsheet
and then importing the source data into the new table in the current database.
I want to use the data import worksheet which is shown here. My first row contains column
headings, so I want to make sure that I check this checkbox.
Now as I look through my data, I have two many to many relationships.
I have a many to many relationship between the movie and the actor. An actor can appear
in many movies and a movie can have many actors. I also have a many to many relationship between
customer and movie. A customer can rent, or rather, a student can create, many movies
and a movie can be entered by many students. I can only use the Table Analyzer Wizard to
handle one many to many relationship, so I am going to handle my customer relationship
in a different way. So I will skip my customer field for now.
I will let access create my primary key and finish the import wizard.
So I now have my data in Microsoft Access, 342 records, and it appears just as it did
in Excel, except now I have primary keys and I am missing my customer field. I will click
on the Database Tools Ribbon and click on Analyze Table.
I want to analyze my data import table. I want to decide what fields are put in what
tables. At this point, I will split my tables up and create several tables. I will first
pull over all of my movie related tables and give this table the name of movie.
Notice the one to many relationship that exists between the original table and the new movie
table. Now I will drag the actor table, the actor information, into its own table. I am
going to give this table the name Actor. I now have a one to many relationship between
the actor and table 1, and a one to many relationship between movie and table 1, so this table 1
will now act as my Association table, creating a many to many relationship. At this point,
I'm going to rename table 1 to cast. So an actor is cast in a movie and a movie
is cast with multiple actors. I will also take and drag my director and create a new
table, called director. Now there are some possible corrections that
Access might make for me automatically in my actors table, so I want to take a look
at this. For example, it wants to change Benjamin Bratt to Billy Boyd. I don't want this to
happen, so I will select leave as is, and do this for each of these incorrect changes.
Now there is a correction that I do want to make sure happens. Eva Mendez, her last name
is spelled with a S, not a Z, so I will make sure that correction is in effect.
At this point, I've made all my corrections. It will ask me if I want to create a query.
I will click on no. So it has created my four tables for me. It
has foreign keys as lookup fields. So far, we have imported data with one many
to many relationship and one one to many relationship. We will need to do some more work in the next
video, and we will use some complex queries, such as Make Table, Append, and we will create
some ad hoc joins.