My data is not simple. The main view sheet looks at about twelve sheets
for its compilation.

Each "table" (sheet) is comprised of a single ID column (field) and some
data, like title or such. That would be quite normalized if I were to
import each of those tables into access as separate tables, no?

Well, the "trick" or concept here is that you DO NOT want to create (well,
import)
a table for just one piece of information. Normalizing your data simply
means you build
a correct data model that models your needs. So, if you have a field that is
the
description for the movie, then you do NOT want to add the extra complexity
of
creating a extra table. If you ONLY have one field, and it not repeating
data (like
many actors), then that field should be in your main table. So, to answer
should
you import this data into "many" tables, the answer is only going to be yes
if
that data is repeating. So, if you have a table that has "many" actors, then
that would continue to be another table. So, we would have two tables so far

tblDVD
--- tblActorsInMovie

So, simply making lots of tables is generally not a good idea for the sake
of making more tables. You ONLY create related tables if the related data
is repeating. In your case, repeating data could be in these extra lookup
tables, or even in the main table (eg: actor1, actore2 is two fields, but
they should be like the above setup, and this I can have as many
actors for a single dvd movie as I need. (no hard coding of how many
actors with fields named actor1, actor2 etc.).


All I would have to do presumably is tie them all together with the ID
field, which is the unique identifier for each.

You main tblDVD would use autonumber id. For child tables, they need
a standard long number field that will be used to point back to the main
tblDVD id. So, the child tables don't really need autonumber fields (but,
you can and should consider just leaving the default "id" as the primary
key for those child tables. All of those child tables will need a plane
Jane standard long number field for the relation. When you add a child
record YOU MUST SET this value. (if you use a sub-form,t hen ms-access
will set this value for you if you setup the relational correctly).



It is not clear if you have "repeating" data for a single dvd (by
repeating,
I mean "many" actors can be attached to one movie).

I intend on incorporating an actors list for each title as well. In
that table, each actor would have a list of ID numbers associated with
each film he or she has appeared in.

Well, don't confuse the actors table with that of "tblActorsInMove".

In our above design so far, we don't have a table of actors. however, that
table could be created, but it not really going to be a enforced
relationship. It just a table of actors.

So, we have now:

tblDVD
--- tblActorsInMovie

tblActorsInMoive will be:
fields:
id dvd_id actor_id

Now, it going to be very hard to remember the actor_id, and have to type in
the id (primary key) of the tblActor table (the pk of all of our tables is
assumed to be id). So, in our form for tblActorsInMove, we likely use a
combo box that lets us view/search by actor name, but stores the id.

So, we have to crate a table for the across (since then we only every type
in the actor name once).

tblDVD
--- tblActorsInMovie

tblActors

Note how I not really set tblActores up in the relation. It not really an
enforced relation. If I add a new DVD, or delete a DVD, we not going to do
anything to the tblActors. It just a simple table of actors that we have.
However, we will MOST certainly delete the list of actors that belong to
that dvd in the tblActoresInMove table when we delete a dvd.

Problem is that I knew how to set up such relationships in Paradox quite
easily, as I was used to the API, and interface for construction of the
database app.

The above setup should get you started. I suppose, you could relate the
tblActoresInMove to the tblActores (you can do this, but it would really
only be for documenting purposes - will not actauly do anything for you)


No. Each title has a single ID number, and several pieces of data to go
with it, like UPC code, sound, aspect, etc.

Well, if you don't have any repeating data, then just place all of the
fields
in one main table. Extra tables will be just extra work, and not benefit you
in any way. IF you still need, or use the "id" you currently have, then
have it exposed in a field, but I would continue to use the internal
autonumber
id for the relationships stuff (you, or your end users will NEVER even see
this internal autonumber...as it just for the relationship, and not for
humans to see, or use as some identification for the dvd.


I have a complete database. I have a complete actors list and
directors list.

Great, so now we have:

tblDVD --- tblActorsInMovie

tblActors
tblDirectoors

So, we have ONE standard long number field in the tblDVD that will store the
tblDriecots
(that means we will have only ONE director for each DVD). You could as
mentioned type in this value by memory, but it going to be hard to know the
directors "id" number to type into that field in tblDVD (once again, we use
a combo box on the form. And, once again, this is not really relationship,
but more a lookup value for our easy sake.

So, would consider the diagram like:

tblDVD --- tableDirctors
--- tblActorsInMovie---tblActors



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada