locked
Problem with table relationship RRS feed

  • Question

  • 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"


    • Edited by Zorba252 Thursday, May 14, 2020 9:49 AM
    Thursday, May 14, 2020 9:46 AM

All replies

  • What is the overall objective of this database? Without that we are only guessing that you have rooms with beds (and no rooms without beds???) and that beds have items ("item" is a very vague word, what do you mean? the bed frame, mattress, side table???)

    IDs should be Autonumber.


    -Tom. Microsoft Access MVP

    Thursday, May 14, 2020 1:20 PM
  • I agree with Tom that a description of the real world scenario you are attempting to model would help us to help you.  I do note the following points, however:

    1.  The primary key of tbl_bed should be bedNo and roomID should be a foreign key referencing the primary key of tbl_room.

    2.  You have incorrectly included a roomID column in tbl_item.  With the changes described in 1 above bedNo is a foreign key referencing the primary key of tbl_bed.  To include a roomID column in tbl_Item, therefore, would introduce redundancy, as it would be transitively dependant on the key, and the table would consequently not be normalized to Third Normal Form (3NF), leaving it open to the risk of update anomalies.

    As regards the user interface, you have a number of options:

    1.  You could have a rooms form, in single form view in which is embedded a beds subform, also in single form view.  In this subform an items subform, in continuous forms view could be embedded.

    2.  You could have a rooms form, in single form view in which is embedded a beds subform in continuous forms view.  Also embedded in the parent form would be an items subform in continuous forms view.  This would not be linked to the parent form in the usual way, but correlated with the beds subform by basing it on a query which references as a parameter a control in the beds subform bound to its primary key.

    You'll find examples of both nested and correlated subforms in CorrelateSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file uses Northwind data to illustrate these types of interface.

    3.  You could have a beds form, in single form view, in which a combo box is bound to the roomID foreign key column.  The NotInList event procedure of the combo box would be used to insert a new row into tbl_room where necessary.  Within this form would be an items subform, in continuous forms view, linked to the parent form in the usual way.



    Ken Sheridan, Stafford, England

    Thursday, May 14, 2020 11:52 PM
  • 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

    you can find the database as below link

    https://1drv.ms/u/s!AsQXVlsFg7mMftl6Qoci5u5rsy8?e=0orhy7

    Saturday, May 16, 2020 12:06 PM
  • Thank you for Your response

    you can find the objective of my project in previous reply, and my database in below link

    https://1drv.ms/u/s!AsQXVlsFg7mMftl6Qoci5u5rsy8?e=0orhy7

    Saturday, May 16, 2020 12:10 PM