Results 1 to 4 of 4

Populate multiple records from information in another table

  1. #1
    Kristin is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    2

    Populate multiple records from information in another table

    I am very new to Access. I have 2 tables. In the first I have Furniture items in the other I have the rooms that need the item(s). I would like to, use a form that will allow me to enter an item in the Furniture table and use that item to populate multiple rooms. For an example I have a Chair in the furniture Table ( this contain make model size color vendor...) I would like to tell the other "table Rooms" (containing type, function, assigned owner...) That the specific chair is in Room 1,2,3,4,5. Thanks.. I am a new.
    I have tried it with a look up field where I can add multiple items. I can see the items listed in a field. Do I need to creat a new table to update so the records reflect room and item? I would do this via a query?
    Last edited by Kristin; 03-27-2012 at 08:04 AM. Reason: Been thinking

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You will need a third table to hold the combination of furniture items and rooms (a room can have many furniture items and a furniture item can be in many rooms: many-to-many relationship)

    I would recommend that you have a primary key field in all 3 tables. Here is a basic structure

    tblRooms
    -pkRoomID primary key, autonumber
    other room related fields

    tblFurnitureItems
    -pkFItemID primary key, autonumber
    -txtItemDescription

    tblRoomFurniture
    -pkRoomFurnitureID primary key, autonumber
    -fkRoomID foreign key to tblRooms (this must be a long integer number datatype field)
    -fkFItemID foreign key to tblFurnitureItems (this must be a long integer number datatype field)

  3. #3
    Kristin is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    2
    Thank-you. I understand your concept. But, I am confused with "foreign key" Is the concept that the room would be one field and the furniture the other. There would only be 3 fields Auto Number Key (primary). room and Furniture? When I open new table the first is auto nmber. To create the next field I pull down options I would chose ? Thanks. When I said I was new I am new. I really haven't done this since Dbase days....

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    But, I am confused with "foreign key" Is the concept that the room would be one field and the furniture the other
    That is correct; I include an autonumber primary key in each table, so you will have 3 fields in this table. The foreign key field relates back to the primary key of the table to which it is related.

    For example, let's say the data in tblRooms looks like this

    pkRoomID|txtRoomNumber
    1|222
    2|333
    3|444

    Then let's say that the data in tblFurnitureItems looks like this:
    pkFItemID|txtItemDescription
    1|Chair
    2|Bed
    3|Sofa

    Now let's say that room 222 only has a chair and sofa. The tblRoomFurniture would have 2 records for that. Let's say that room 444 has only a bed--so 1 record for that.

    pkRoomFurnitureID|fkRoomID|fkFItemID

    1|1|1 (room 222, chair)
    2|1|3 (room 222, sofa)
    3|3|2 (room 444, bed)


    When creating the other 2 fields (1 for the roomID and the other for the furniture item), you would type in each name and select Number from the datatype drop down. If you look in the lower pane for field size (General tab), it should default to long integer which is what you want. The data types of the primary key and its foreign key must be of the same datatype in order to establish a relationship. The autonumber datatype is just a special case of a long integer number.

    I would not use any lookups (tab in the lower pane along side the General tab) in your table (even though Access has the capability); table level lookups can cause problems as detailed on this site

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-05-2011, 01:55 PM
  2. Replies: 12
    Last Post: 08-30-2011, 03:36 PM
  3. Replies: 12
    Last Post: 04-18-2011, 08:52 AM
  4. Replies: 3
    Last Post: 10-06-2010, 06:33 AM
  5. Referencing multiple records in a table
    By akbigcat86 in forum Programming
    Replies: 14
    Last Post: 07-22-2010, 01:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums