title: Same functionality as Inner join on not linearly linkable tables
body: <p>Im currently designing a list in VBA (Access 2003) that uses several SQL queries in forms and subforms to fetch information from a centralized Database (tables are all linked via ODBCs in Access, Provider is 2010 SQL Native Client 10.1, SQL Server is in MSSQL)</p>
  
  <p>Im currently working on a Form that fetches all fields from a singular Table, and then the Sum of a fields from a whole host of records in a secondary table. </p>
  
  <p>My current SQL Query is this:</p>
  
  <pre><code>SELECT Chemikalien.*, (SELECT SUM(Bewegungen.Bewegung) FROM Bewegungen WHERE Bewegungen.Bestand_ID = Chemikalien.Bestand_ID AND Bewegungen.Typ = 5) As Umlaufmenge FROM Chemikalien 
  </code></pre>
  
  <p>This is currently not working. It does not return an error, but it will not fetch anything from the Inner SQL Query (the Sum() part).</p>
  
  <p>To be clear the intended behavior is:</p>
  
  <ul>
  <li>Fetch all fields from the table <code>Chemikalien</code></li>
  <li>Fetch the Sum of all values from the Field <code>Bewegung</code> in the table <code>Bewegungen</code> where the field <code>Bestand_ID</code> in the table <code>Bewegungen</code> matches the field <code>Bestand_ID</code> in the table Chemikalien and the field <code>Type</code> in the table <code>Bewegungen</code> equals <code>5</code></li>
  </ul>