Table Design

[Karen]

Hi,

I have created a database that tracks equipment.

I created a table called Location with the following
fields:
Loc_Id (auto_number and primary key)
Bldg
Room
Cubicle

Then I created another table called Asset_Details that
holds the details of the equipment. Loc_Id is a foreign
key in this table with number as a data type in the table
design.

In the relationships view I added the Location Table and
Asset Details table and when I try to link the Loc_Id's
together to create referential integrity I get an error.
Do they both have to be the same data type. I am able to
join them without using referential integrity but I want
to be able to update a bldg on a form and make sure that
both tables will be updated.

Any help is appreciated..

 

[AndyK]

what is the field size of the foreign key Loc_ID set to - if Long Integer it should work fine but if anything else (eg Integer) trying to enforce referential integrity in the relationships window gives an error.

If it is set to Long Integer and you still have a problem what is the actual error message you get when you try to enforce referential integrity?

 

[Steve Schapel]

Karen,

One thing that will cause an error in this situation is if there is
already a record in the Asset_Details table with an entry in the Loc_Id
field where there is no record in the Location table with this same
Loc_Id value.

I am not sure, but I think you may have misunderstood the purpose of
Referential Integrity. "I want to be able to update a bldg on a form
and make sure that both tables will be updated" is *not* what it's about!

 

[Karen]

-----Original Message-----
what is the field size of the foreign key Loc_ID set to -

if Long Integer it should work fine but if anything else
(eg Integer) trying to enforce referential integrity in
the relationships window gives an error.

If it is set to Long Integer and you still have a problem

what is the actual error message you get when you try to
enforce referential integrity?

.
So I removed the field from the asset_details table and

then re-added it and I was able to create referential
integrity. One thing I'm not sure of though is when I
create my form I will use the data from the asset_details
table. How will the users be able to choose the bldg,
room and cubicle by reference of a Loc_Id. Is there any
way I can do this without using a sub-form?

 

[DDM]

Karen, the short answer to your question is that you can open your
Asset_Details table in design view, select the Loc_Id field and use the
Lookup Wizard to create a combo box that will display the Loc_Id together
with the corresponding Bldg/Rm/Cubicle. You'll want to create a query that
includes all the fields from Asset_Details plus the Bldg, Room, and Cubicle
fields from the Locations table. Then create your form from that query. Your
users will be able to selest a location by picking a Loc_Id from the list.
They will be able to change the Bldg, Room, or Cubicle for any location, and
they will be able to create new locations by entering Bldg, Room, and
Cubicle information.

Which brings up another point: Why have a locations table at all? Why not
just add three fields to your Asset_Details table? Is there a special
significance to a particular comination of Bldg/Room/Cubicle that merits
calling it a "location" and tracking it in its own table? Just a thought.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Steve Schapel]

DDM,

Karen's design is good in the area you are questioning. Assuming she
can have more than one Asset in any given Location, she has set up her
tables according to correct relational principles.