Relating Tables in a database automatically

[BlackFireNova]

I have an existing database, and I need to add another table to it. The
database tracks equipment, however I have a need to track ancillary
items which are purchased or added to some of the equipment separately.

In the existing table there is a serial number field, which is indexed
with no duplicates. In the new table there will be multiple entries
which must be related to the individual piece of equipment which is
entered in the 'primary' (existing) table.

_______________ _______________ _______________ _

I have used the table wizard on occasion, to split a table, and I note
that when it "splits" a table it sets up a field in each table which
seems to automatically relate the two new tables, and make them fully
relational.

I can't see in the help, or in the various reference manuals an
explanation of the mechanics of how this is accomplished.

Maybe this is something that Access keeps track of and does
automatically once the tables are related in the Relationships Window,
and I just am failing to grasp how the 'Relational' part of the database
works. If that is the case, though, then why the special fields
inserted into the two separate tables which result when using the Table
Wizard to split up a table?
_______________ _______________ _______________ _________

Can anyone explain, and demonstrate, how I can implement this automatic
method of relating tables so I could add my new table to the database
and have new record entries to the new table automatically relate to the
existing table as new entries are added?
(Or alternately, why I don't need to, and why??)

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phobos]

Look up "Relationsh ips" in help.

P
"BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message
news:3f******** *************@n ews.frii.net...

I have an existing database, and I need to add another table to it. The
database tracks equipment, however I have a need to track ancillary
items which are purchased or added to some of the equipment separately.

In the existing table there is a serial number field, which is indexed
with no duplicates. In the new table there will be multiple entries
which must be related to the individual piece of equipment which is
entered in the 'primary' (existing) table.

_______________ _______________ _______________ _

I have used the table wizard on occasion, to split a table, and I note
that when it "splits" a table it sets up a field in each table which
seems to automatically relate the two new tables, and make them fully
relational.

I can't see in the help, or in the various reference manuals an
explanation of the mechanics of how this is accomplished.

Maybe this is something that Access keeps track of and does
automatically once the tables are related in the Relationships Window,
and I just am failing to grasp how the 'Relational' part of the database
works. If that is the case, though, then why the special fields
inserted into the two separate tables which result when using the Table
Wizard to split up a table?
_______________ _______________ _______________ _________

Can anyone explain, and demonstrate, how I can implement this automatic
method of relating tables so I could add my new table to the database
and have new record entries to the new table automatically relate to the
existing table as new entries are added?
(Or alternately, why I don't need to, and why??)

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PMB]

The easiest way to create relationships I've found is create the second
table, then in the primary or parent table, put a field linking the two, use
lookup and link to the other table.

HTH

Michael
"BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message
news:3f******** *************@n ews.frii.net...

I have an existing database, and I need to add another table to it. The
database tracks equipment, however I have a need to track ancillary
items which are purchased or added to some of the equipment separately.

In the existing table there is a serial number field, which is indexed
with no duplicates. In the new table there will be multiple entries
which must be related to the individual piece of equipment which is
entered in the 'primary' (existing) table.

_______________ _______________ _______________ _

I have used the table wizard on occasion, to split a table, and I note
that when it "splits" a table it sets up a field in each table which
seems to automatically relate the two new tables, and make them fully
relational.

I can't see in the help, or in the various reference manuals an
explanation of the mechanics of how this is accomplished.

Maybe this is something that Access keeps track of and does
automatically once the tables are related in the Relationships Window,
and I just am failing to grasp how the 'Relational' part of the database
works. If that is the case, though, then why the special fields
inserted into the two separate tables which result when using the Table
Wizard to split up a table?
_______________ _______________ _______________ _________

Can anyone explain, and demonstrate, how I can implement this automatic
method of relating tables so I could add my new table to the database
and have new record entries to the new table automatically relate to the
existing table as new entries are added?
(Or alternately, why I don't need to, and why??)

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

uh oh... time to learn something.

You need to create the "tblAncillaryIt ems" table...
In it you need an ID field for the Ancillary Item to uniquely identify
it. (AItemID)
Then you need the primary key (EquipmentID) from the original table,
"tblEquipme nt". Join on
tblEquipment.Eq uipmentID=tblAn cillaryItems.Eq uipmentID

cascade update = Yes.
cascade delete = ? (what happens when someone loses a piece of
equipment?)

*Or* Can a single piece of ancillary equipment go into several pieces
of equipment over time? then you have a different set of primary keys
for Ancillary Equipment... Might be time to crack open a database
theory book... maybe "Database Design for Mere Mortals" by Michael
Hernandez?