By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Relating Tables in a database automatically

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Look up "Relationships" in help.

P
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.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!

Nov 12 '05 #2

P: n/a
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
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.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!

Nov 12 '05 #3

P: n/a
uh oh... time to learn something.

You need to create the "tblAncillaryItems" 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,
"tblEquipment". Join on
tblEquipment.EquipmentID=tblAncillaryItems.Equipme ntID

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?
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.