+ Post New Thread
Results 1 to 6 of 6
Office Software Thread, Access Help required in Technical; I have an Access database with tables for (among other things) equipment and rooms, I would like to link these ...
  1. #1

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22

    Access Help required

    I have an Access database with tables for (among other things) equipment and rooms, I would like to link these two so I can see bring up a room and see what equipment is in it. The basic set up is OK - I've created the link table with a compound primary key all of which works fine. What I am looking for is a way of populating the link table without having to do it all record by record. I'm sure it must be possible to take a list of rooms from the rooms table and add the code for the item from the equipment table in one operation eg all IT rooms have a whiteboard so I want to take rooms IT* and link them to the whiteboard code but having tried all sorts of variations of update and append queries I can't get it to work. Any suggestions?

    The best I've come up with so far is using a make table query to get the subset of rooms add a field to this for the equipment code, update the equipment code and then append these records back to the link table but this isn't really any quicker than updating each record manually

  2. #2

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    So are you looking for a report of Rooms with corresponding equipment?

    You shouldn't want to 'populate' a link table, it is replication of the data that already exists in the other 2 tables.

    Say you have 2 tables like this, with the first in the column being the primary key

    Code:
    Room No        EquipmentID          EquipementInRooms  
    Building          Type          Room No
    Campus          Warranty           Equipment
    Extension        
    Teacher
    Equipment
    Your third table EquipmentInRooms is populated automatically when you set up the one-to-many and many-to-many relationships.

    Its kind of hard to explain in text. Post your database (or a subset of data or just the table structure if you like) and Ill tkae a look at it for you and try to help

  3. #3

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    The table structure is roughly as you outline above but the equipmentinrooms doesn't populate automatically. The relationship between tblrooms and tblequipment is many to many so I assume a link table is needed but somewhere you need to tell it that roomNo 1 contains EquipmentID 1, equipmentid 3 etc and roomNo2 has equipmentID 2, equipmentID 3 and so on this is the bit I am trying to automate. The problem has arisen because I want to add the rooms table to an asset database that didn't have it originally.

  4. #4

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,512
    Thank Post
    1,320
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    how are you listing equipment? Is it general "smartboard" "table" "chair" "projector" or do you have all your smartboards listed in the table, eg Type=Smartboard

    as if it is the latter way, it wouldnt be many to many I dont think, as 1 smartboard can only be in 1 room.

    You can have many smartboards in many rooms, but if you are linking it on individual asset allocated to individual room, its 1-1 (i think)

    edit: but then again, 1 room can have many assets, so It might be 1 - many.. I cant think of it in my head, and its getting late.

    Someone else will hopefully be able to explain better than I can.

  5. Thanks to RabbieBurns from:

    Hecate (30th June 2011)

  6. #5

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    I have all the assets in one table so my fields are EquipmentID; EquipmentName: EquipmentType; Make: Model; etc etc. ......Just realised I think you are right each asset can only be in one room so it's one to many not many to many. My head's spinning too now so I'll look at it again tomorrow

  7. #6

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    Thanks Rabbie for trying to help - after sleeping on it and looking again I realised that in trying to simplify the problem for posting I had over simplified it and picked a bad example. I'm now modifying the design to get what I want another way but I still think it should be possible to set up an intersection for a many to many relationship and populate it without having to do it one record at a time. (I don't mean enter data into the link table directly just set up the queries which make the links and write to the table.)Anyway thanks again you at least helped me get my thoughts in order

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 1
    Last Post: 19th April 2010, 03:50 PM
  2. Replies: 5
    Last Post: 4th December 2009, 11:50 PM
  3. Replies: 1
    Last Post: 23rd October 2009, 08:53 AM
  4. Replies: 3
    Last Post: 4th September 2009, 09:08 PM
  5. Replies: 20
    Last Post: 20th August 2009, 09:40 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •