Relationships

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

In Access 2002 I am trying to set up a 1-1 relationship
and yet have a form with building/room numbers and a
subform which shows all equipment in that location (so
that when I type in Item#, all other info plugs in, such
as description, etc.) However, I want the relationship to
be a 1-1 so the same equipment item cannot be placed in 2
places at the same time. Is this possible? I have had to
set up 2 primary keys on Item ID and Room# which creates
a 1-many relationship for this to work, but it is still
possible to place the same item in 2 different rooms. Any
way around this? Thanks!
 
Try this table setup as an example:

tblBuildings
BldgID (primary key)
BldgName
BldgLocation
(etc.)

tblRooms
RoomID (primary key)
RoomName
BldgID (foreign key)
(etc.)

tblEquipment
EquipID (primary key)
RoomID (foreign key)
EquipName
(etc.)
 
Back
Top