Problem with table relationship

[Pop_Access]

• I intend to design new Access databased, I created 3 tables
  1- tbl_room, PK roomID "Short Text"
  2- tbl_bed PK roomID "Short Text" AND bedNO "Number"
  3- tbl_item PK itemID "Number", bedNO "Number" AND roomID "Short Text"
  I want to create correct relationship between them; knowing that the relationship should be as below:-
  tbl_room with tbl_bed (one to many)
  tbl_bed with tbl_item (one to many)
  I already designed form (frm_room) which contains two sub forms (tbl_bed subform & tbl_item subform) to enter the data through it.
  when I tried to connect tbl_bed with tbl_item I faced message "No unique index found for the referenced field of the primary table"
  please see the attached file.

 

[Ranman256]

you need another table (to relate) tBedItems, with these 2 keys*
BedItmID (autonum)
bedNO*
itemID*

THIS relates its data and is FED from tItems.
tItems does not get BedNo field, nor RoomID

 

[mike60smart]

• I intend to design new Access databased, I created 3 tables
  1- tbl_room, PK roomID "Short Text"
  2- tbl_bed PK roomID "Short Text" AND bedNO "Number"
  3- tbl_item PK itemID "Number", bedNO "Number" AND roomID "Short Text"
  I want to create correct relationship between them; knowing that the relationship should be as below:-
  tbl_room with tbl_bed (one to many)
  tbl_bed with tbl_item (one to many)
  I already designed form (frm_room) which contains two sub forms (tbl_bed subform & tbl_item subform) to enter the data through it.
  when I tried to connect tbl_bed with tbl_item I faced message "No unique index found for the referenced field of the primary table"
  please see the attached file.

Hi Take a look at the modified Database.

Look at how the tables are now related to each other.

I have Colour Coded the Primary Key as Green in table Rooms and also Colour Coded the Primary Keys in each of the Other two tables.

The Foreign Key in each of the related tables are also Colour Coded Green.

Also take note of what Ranman has said about how you should related the Bed to Items

 

[Cronk]

I think the database has design flaws. If you have an entity Room then that will contain instances of Item, including beds. Any room booking system I've been involved with, the only issues with beds are the number of beds and size eg single, double, cot.

 

[Pop_Access]

you need another table (to relate) tBedItems, with these 2 keys*
BedItmID (autonum)
bedNO*
itemID*

THIS relates its data and is FED from tItems.
tItems does not get BedNo field, nor RoomID

Thanks a lot,

i tried to create new table, but the same message i faced. "No unique index found for the referenced field of the primary table".

 

[Pop_Access]

thanks for all your support,

i would to describe my objective of this project;
i have 3 buildings each building contains floors and each floor contains rooms each room contains beds (from 1 bed upto 6 beds).
When reserving a bed for a guest, a set of materials becomes in his custody (wardrobe, refrigerator ...) Note that each material has a unique number and each room also has a unique number, what is required, when choosing the room number and the bed number in it is to know the existing materials In this room

 

[mike60smart]

How would you want to manage the data input?

Do you receive a phone call from a Guest to Book a Room ?

 

[Pop_Access]

How would you want to manage the data input?

Do you receive a phone call from a Guest to Book a Room ?

The Receptionist will enter the data, and i do not want to manage the booking issue

 

[Pop_Access]

I think the database has design flaws. If you have an entity Room then that will contain instances of Item, including beds. Any room booking system I've been involved with, the only issues with beds are the number of beds and size eg single, double, cot.

Thank you,

i do not need to manage the booking, i want to manage the items in the rooms

 

[mike60smart]

If you do not have a means to manage the rooms in the database How will the receptionist know which rooms are free?

 

[Pop_Access]

If you do not have a means to manage the rooms in the database How will the receptionist know which rooms are free?

this will be manual

 

[mike60smart]

OK so you don't need a database then

 

[mike60smart]

Also How can 1 Room have Many Beds?

Only instance of this is a Barrack Room in the Military

 

[The_Doc_Man]

Many motels have suites with Twin Queen beds rather than one King bed. And when I was in Ft. Worth in 2005 after Katrina, my wife, mother-in-law, and grandson shared a four-bed suite (two Twin Queens).

 

[mike60smart]

Hi Doc_Man

I am trying to get an answer as to why the Op does not want to manage the allocation of rooms using a database as this is to be carried out by the Receptionist?

How would the Receptionist know what is available if she is not allowed access to the database?

All I am trying to ascertain is how they want to control Data Input.

Currently the structure is all wrong.

 

[Cronk]

In #9, the OP is stating he only wants his db to manage the items in the rooms. It's more of an inventory system than a booking system.

As I see it, the structure would be
tblBuildings
BuildingID PK
BuildingName
...other building fields

tblRooms
RoomID PK
BuildingID
RoomNo

tblRoomItems
tblRoomItemsID PK
ItemID

tblItems
ItemID
ItemTypeID
SerialNo
..other Item fields

tlkpItemType
ItemTypeID
ItemDescription

ItemType would include single bed, double bed, microwave, TV, bedside table, chairs etc.

I still can't see how a bed would have any items in a practical sense unless micro-management of sheets, pillows, blankets is required.

 

[mike60smart]

I agree with Cronk

I cannot see how 1 bed would have multiple items to be allocated.

 

[Cronk]

Sorry, there was an omission of RoomID in tblRoomItems in my #16. It should have been

tblRoomItems
tblRoomItemsID PK
RoomID
ItemID