Relationship setup - CAFM

[vbcraft]

Hi,
Maybe someone can help me with a relationship setup for the following senario:
I have the following tables, furniture, Building, Floor, and RoomCode.
I want to have in my furniture table the building_id, floor_id, and room_id.
I want to create relationships which would enforce referential integrity. I
have tried to set this relationship up but I get errors.
Point me to any example and I would really appreciate it.
FYI> Building table has building_id (makes PKEY)
Floor table has building_id and floor_id (both make PKEY)
RoomCode table has building_id and floor_id and room_id (all three make PKEY)

 

[KARL DEWEY]

Seems to me you only need three tables --
Building --
BLDG_ID - autonumber - primay key
BLDG_Name
Floor
RoomCode
Purpose

Furniture --
FurnID - autonumber - primay key
FurnType - text - chair, table, bookcase, desk, etc.
QTY - number

BLDG_Furn --
BLDG_FurnID - autonumber - primay key
FurnID - number - long integer - foreign key
BLDG_ID - number - long integer - foreign key

Set one-to-many between Building and BLDG_Furn. Also one-to-many between
Furniture and BLDG_Furn.

Use form/subform for Building to BLDG_Furn (Furniture), using a combo box to
select Furniture. Use BLDG_ID for Master/Child links.
Use separate form/subform for Furniture to BLDG_Furn (Building), using a
combo box to select Building. Use FurnID for Master/Child links.

 

[Steve]

How about ........

TblBuilding
BuildingID
etc

TblFloor
FloorID
BuildingID
FloorNumber
etc


TblRoom
RoomID
FloorID
RoomNumber
etc

TblFurniture
FurnitureID
FurnitureDescription
FurnitureNumber
etc

TblFurnitureInRoom
FurnitureInRoomID
RoomID
FurnitureID
DateFurnacedPlacedInRoom
etc


Steve
(e-mail address removed)

 

[vbcraft]

I will look into these suggestions! thank you so much!