Relations - Keep it simple please

  • Thread starter Thread starter Lynne Eske
  • Start date Start date
L

Lynne Eske

Hello

I'm just starting out with Access and I'm trying to get to grips with
relationships.

If I have two tables, then to make them related I have to have the same
primary key in both ?
Is that right ?

So.........

In order for the relationship to work, I have to populate both tables with
information in each of the fields but specifically I need to enter the
primary key value twice ? Once in each table ?

Sorry if I'm confused here but that means typing the same thing twice (once
in each primary key field in each table) and if I miss one record in one
table then the relationship is no longer valid ?

And then if I want to enter a new record or update an existing record where
the primary key field is new or changes - I have to do this in both tables
again ?

Please help !!


Lynne E.
 
No, no, no, no (eek!)

A primary key is a unique identifier for the rows in a table. If you have
another table, it needs unique identifiers for the rows in it. What makes
the tables related is the fact that one of the tables stores a "copy" of the
primary key FROM THE OTHER TABLE.

Think of parents and children. You have a table of parents. Each has a
unique identifier (i.e. ParentID, a primary key). You have a table of
children. Each has a unique identifier (i.e., ChildID, a primary key). But
which children belong with which parents? In your Child table, you need a
field that stores something like [MyParentID]. This is called a "foreign
key" in the Child table.

All this assumes that you have a one-to-many relationship between table1 and
table2.

If you have a different relationship between the tables, post back with more
information.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
No. One table (the child table) will have a foreign key that's the same
value as the primary key in the parent table. That has no bearing on the
primary key in the child table, though (although sometimes the primary key
of the child table might be a compound key that includes the foreign key
plus one or more other fields)
 
Oh dear .............

Its not easy is it !

Ok

I have one table called tbl_drives and another called tbl_drawings.

In tbl_drives there are the following fields..........

drivenumber (primary key ?), drivetype, drivesize

In tbl_drawings there are these fields...........

drivenumber (foreign key ?), drawingnumber

Thre are many duplicate entries of drivenumber and similarly with drawing
number in tbl_drawings.

The field called drivenumber in each table needs to be related.

If I enter a new record in tbl_drives how does that put a new record in
tbl_drawings ?

Similarly If i change say the drivenumber value of a record in tbl_drives
how does it update the record in tbl_drawings ?


I'm confused - sorry !

Lynne E.
 
First, relationships do not cause data to be entered into more than one
table. Entering data into tbl_drives will not cause anything to be entered
into tbl_drawings, whether or not there's a relationship.

All a relationship does is indicate the relationship between data in two
tables. You can set referential integrity on, so that you cannot enter data
in, say, tbl_drawings unless a corresponding record exists in tbl_drivers,
and you can have Access delete any related records in tbl_drawings if you
delete a record from tbl_drivers (known as cascade delete), and you can have
the value of drivenumber in tbl_drawings updated if it changes in tbl_drives
(known as cascad update), but that's it. (As far as I'm concerned, though, a
primary key should never be changed, so I question the validity of cascade
update!)
 
Douglas J. Steele said:
First, relationships do not cause data to be entered into more than one
table. Entering data into tbl_drives will not cause anything to be entered
into tbl_drawings, whether or not there's a relationship.

All a relationship does is indicate the relationship between data in two
tables. You can set referential integrity on, so that you cannot enter
data in, say, tbl_drawings unless a corresponding record exists in
tbl_drivers, and you can have Access delete any related records in
tbl_drawings if you delete a record from tbl_drivers (known as cascade
delete), and you can have the value of drivenumber in tbl_drawings updated
if it changes in tbl_drives (known as cascad update), but that's it. (As
far as I'm concerned, though, a primary key should never be changed, so I
question the validity of cascade update!)
Click to expand...

So .............

In order to make the relationship work - I have to maintain the same
(linked) fields in both tables ?

So I have to type in the common denominator twice ?

Sorry for being a newbie !

Lynne E.
 
Lynne Eske said:
So .............

In order to make the relationship work - I have to maintain the same
(linked) fields in both tables ?

So I have to type in the common denominator twice ?

Sorry for being a newbie !

Lynne E.
Click to expand...

To relate records in two tables, yes the same values(s) must exist in both
tables: what's the primary key for the one table (which can be up to 10
separate fields) must be duplicated as the foreign key in the second table.

Usually this is done through the form, though, so you shouldn't have to
retype the data.
 
In order to make the relationship work - I have to maintain the same
(linked) fields in both tables ?
Yes.

So I have to type in the common denominator twice ?
Click to expand...

Only if you're entering data directly into Tables. That's one (of a
great many) reasons not to do so!

If you use a Form based on the "one" side table, with a Subform based
on the "many", you can set the Subform's Master and Child Link Field
properties to the linking fields; Access will automatically keep them
in synch.

Use the tools Access provides. They *do* help.

John W. Vinson[MVP]
 
John Vinson said:
Only if you're entering data directly into Tables. That's one (of a
great many) reasons not to do so!

If you use a Form based on the "one" side table, with a Subform based
on the "many", you can set the Subform's Master and Child Link Field
properties to the linking fields; Access will automatically keep them
in synch.

Use the tools Access provides. They *do* help.
Click to expand...


Thanks for the help

I'll be posting in the forms newsgroup next !!
 
Back
Top