Filling rows across multiple records

  • Thread starter Thread starter Joe Doherty
  • Start date Start date
J

Joe Doherty

I don't know how to write a subject line for this one.

I have multiple records of data from the IMDB. After processing it, each
actor's listing contains his name in Field 1 and the movie he is in in Field
2 (along with other stuff, but I've broken it into these basic fields). In
the second row of each actor's listing (assuming he has more than one entry
in the database) Field 1 is blank; Field 2 contains the name of the movie.
There is a blank line between actors. Like this:

Mickey Mouse Steamboat Willie
Fantasia

Mighty Mouse Saved by the Mouse

I would like to make a database in which the appropriate name is in each row
of Field 1 and the blank lines are removed, like this:

Mickey Mouse Steamboat Willie
Mickey Mouse Fantasia
Mighty Mouse Saved by the Mouse

Any help is appreciated.
Joe Doherty
doherty_at_law-dot-ucla-dot-edu
 
Lets start by saying best of luck!

What you need to do is import all the actors into 1 table (taking care to
assign unique values where there are more than 1 actor of the same name)

Then, import all the movies into a 2nd table again making sure that the
film is uniquely identified by more than the name (ie Taxi and Taxi).

Lastly, you need a 3rd table to link the actor to the film.

Perhaps when you next ask IMDB to supply the data, you request a normalised
form.
 
You have a problem in that the records in Access have no order, so there is no
way to know for sure that (per your example) line 1 is record1 and line2 is
record2 and line3 is record3.

You might be able to solve this problem by importing into records with an
autonumber field to force an order, but even then I wouldn't guarantee it. I
think that I would handle this situation by writing some vba to import the
records one at a time and keeping track of the previous actor and if field 1 was
blank and field2 was not then use the variable to populate field1. If both
fields were blank then skip that record.
 
Back
Top