title: Issue with many 1:M scenario in MS Access
body: <p>First time posting. Long time reader!</p>
  
  <p>I've recently started delving into Access. I have some background/slash knowledge with databasing and I've made good progress on my little project - however, I am stumped when it comes to nesting/many one-to-many's. I'm trying to work it all out as an algorithm first, instead of continuing on in frustration. I vaguely remember that in SQL you can have parent and child forms, where the child form each parent is persistent. Is that the same with MS Access Subforms?</p>
  
  <p>Maybe its a limitation of Access, or maybe I'm just a muppet noob - so I'm hoping someone can point me in the right direction!</p>
  
  <p><strong>Scenario:</strong></p>
  
  <p>Let's consider a building/office: This building has Doors and Rooms. For the sake of the question, let's only consider the Doors and Rooms.</p>
  
  <p>In my scenario, a Room can have multiple doors, whereas a Door can only have a maximum of two Rooms (the two rooms that it joins).</p>
  
  <p>I have a table of Doors and a table of Rooms, each with a PK.</p>
  
  <p>I want to have a list of Doors, that demonstrates what Rooms they are associated with (should only ever be two) and I also want a register of Rooms, that lists all Doors associated with the Room.</p>
  
  <p><strong>Problem 1:</strong> So considering the Rooms scenario, how would I associate it with the Doors Table, in such a way that I can have multiple entries linked to the Room Table. I'm assuming a join them through a third table that links Rooms and Doors.</p>
  
  <p><strong>Problem 2:</strong> In relation to the Doors scenario, what would be the best way? Would I make two columns/fields in the Door Table, like Room_A and Room_B, or would I somehow join them through a third table? </p>
  
  <p><strong>Problem 3:</strong> Further to this, I will need to make a list of all Doors that are accessible in both Room_A and Room_B. I think this part won't be so hard once the two other problems are dealt with.</p>
  
  <p>Before anyone jumps in and says "upload a screenshot you douchebag" - I can't upload anything at the moment. I'll try and upload something later today.</p>
  
  <p>Thanks in advance for any info!</p>