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
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.
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
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