Access Help required

[Hecate]

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

[RabbieBurns]

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

 

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

[Hecate]

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.

[RabbieBurns]

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.

[Hecate]

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

[Hecate]

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