I have a problem with table relationships

[Guest]

Access 2003.

I have trouble linking tables or understanding how this link works. I have
several tables, one listing movies, the others listing geographical regions
where they've been broadcast, and how much the broadcaster payed fro the
licence. Now In the movie table I have a field called: Title. I joined this
in the relationships menu with the Title field in the geographic reports.
However, when moving from one table to the next the title does not fill in.
I've also tried joining the Unique IDs instead of the titles, and have the
Title be a lookup, but it only returns numbers, that can be deleted at
that... If anyone can offer any help...Many thanks in advance.

 

[Keith Wilby]

Access 2003.

I have trouble linking tables or understanding how this link works. I have
several tables, one listing movies, the others listing geographical
regions
where they've been broadcast, and how much the broadcaster payed fro the
licence. Now In the movie table I have a field called: Title. I joined
this
in the relationships menu with the Title field in the geographic reports.
However, when moving from one table to the next the title does not fill
in.
I've also tried joining the Unique IDs instead of the titles, and have the
Title be a lookup, but it only returns numbers, that can be deleted at
that... If anyone can offer any help...Many thanks in advance.

You need a Primary Key field (unique identifier, no dups) in your Movies
table and a corresponding Foreign Key field (dups OK) in your Regions table.
Join on these fields and enforce referential integrity. For every movie
there can be one or more regions, hence a one-to-many relationship.

HTH - Keith.
www.keithwilby.com

 

[Guest]

Dear Keith,
Many thanks for replying. However, I'm afraid I'm not understanding you
completely. I've created another field in my regions table, and linked it to
the unique ID in my movies table. That works fine. Now, how do I get the
Regions table to generate records with the right title when a record is
generated in the movies table? I mean, once i've inputed the data in the
movies table, I want to input data about that movie in my, say, Australasia
table. However, upon going to that table, I don't know which record to
edit...please ask me to clarify if this is not clear;

To give you an idea of what I'm thinking: I created the various tables
simply because I ran out of columns when creating my main table. Initially it
looked something like:

Title Director Australia Buyer Australia Price Germany Buyer
etc. etc.

Since this didn't work I split up the data, hoping to make it work by
linking tables.

 

[Keith Wilby]

Dear Keith,
Many thanks for replying. However, I'm afraid I'm not understanding you
completely. I've created another field in my regions table, and linked it
to
the unique ID in my movies table. That works fine. Now, how do I get the
Regions table to generate records with the right title when a record is
generated in the movies table? I mean, once i've inputed the data in the
movies table, I want to input data about that movie in my, say,
Australasia
table. However, upon going to that table, I don't know which record to
edit...please ask me to clarify if this is not clear;

To give you an idea of what I'm thinking: I created the various tables
simply because I ran out of columns when creating my main table. Initially
it
looked something like:

Title Director Australia Buyer Australia Price Germany Buyer
etc. etc.

Since this didn't work I split up the data, hoping to make it work by
linking tables.

You only need store Title once. The movie has a title so you store it in
your movie table. Let's simplify.

Your Movie table has three fields - MovieID, Title and Director. MovieID is
a unique record identifier (primary key). Your Regions table has four
fields, RegionsID (PK), Region, Price and MovieID (foreign key). Join on
the two MovieID fields and enforce referential integrity. Populate your
Movies table with a few records. Populate your region table, remembering to
use the PK data from the Movies table in the FK field.

Now create a query and include both tables. Include all fields on the query
grid and run the query. A record for each movie will appear for each
region, ie you will have three entries for "Liar Liar" if it has entries for
UK, USA and Australia. So your data might looks like:

Liar Liar Tom Shadyac Australia $15.00
Liar Liar Tom Shadyac UK £10.00
Liar Liar Tom Shadyac USA $12.00

but Title and Director are stored only once, in your Movies table.
Actually, since you want a "buyer" field, have you considered having a
"Customers" table instead of "Region" (since all customers will have a
unique region)?

The way to get the functionality for free is to have a form based on your
Movies table and a subform based on the regions table with parent/child
links on the MovieID field.

HTH - Keith.
www.keithwilby.com