title: How to make a report through multiple table join at a given date in MS Access?
body: <p>I have a few basics in SQL (mostly for spatial analysis with PostGIS) but I want to make a small Access DB to manage data about members in a small organisation. The DB in structured like that :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Members</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>id</td>
  </tr>
  <tr>
  <td>attribut1</td>
  </tr>
  <tr>
  <td>attribut2</td>
  </tr>
  <tr>
  <td>attribut3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>linked by memberId to</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Attribut4</th>
  <th>Attribut5</th>
  <th>Attribut6</th>
  <th>Attribut7</th>
  <th>Attribut8</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>memberId</td>
  <td>memberId</td>
  <td>memberId</td>
  <td>memberId</td>
  <td>memberId</td>
  </tr>
  <tr>
  <td>changeDate</td>
  <td>changeDate</td>
  <td>changeDate</td>
  <td>changeDate</td>
  <td>changeDate</td>
  </tr>
  <tr>
  <td>a4Value1</td>
  <td>a5Value1</td>
  <td>a6Value1</td>
  <td>a7Value1</td>
  <td>a8Value1</td>
  </tr>
  <tr>
  <td>a4Value2</td>
  <td>a5Value2</td>
  <td>a6Value2</td>
  <td>a7Value2</td>
  <td>a8Value2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The goal here is to get the a<em>N</em>Value<em>M</em> for each member at a given date.
  Something like that :</p>
  <p>id, attribut1, attribut2, attribut3, a4Value1, a5Value1, a5Value2, a6Value1, a7Value1, a8Value2</p>
  <p>I managed to make it work for a single member in a form where I give the id and the date, but the struggle appears when I try to make it work to list every member. I tried with subqueries without success, and peculiarities from Access doesn't help my little knowledge in SQL.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
    *
  FROM
    (
      SELECT
        *,
        row_number() OVER(
          PARTITION BY Members.id
          ORDER BY
            Members.id,
            Attribut4.changeDate DESC,
            Attribut5.changeDate DESC,
            Attribut6.changeDate DESC,
            Attribut7.changeDate DESC,
            Attribut8.changeDate DESC
        ) AS rn
      FROM
        (
          (
            (
              (
                (
                  Members
                  LEFT JOIN Attribut4 ON Members.id = Attribut4.id
                )
                LEFT JOIN Attribut8 ON Members.id = Attribut8.id
              )
              LEFT JOIN FONCT_FORM ON Members.id = FORM.id
            )
            LEFT JOIN Attribut7 ON Members.id = Attribut7.initiales
          )
          LEFT JOIN Attribut5 ON Members.id = Attribut5.initiales
        )
        LEFT JOIN Attribut6 ON Members.id = Attribut6.initiales
      WHERE
        (
          (
            (Attribut4.changeDate) &lt;= FORM.date
          )
          AND (
            (Attribut5.changeDate) &lt;= FORM.date
          )
          AND (
            (Attribut6.changeDate) &lt;= FORM.date
          )
          AND (
            (Attribut7.changeDate) &lt;= FORM.date
          )
          AND (
            (Attribut8.changeDate) &lt;= FORM.date
          )
        )
    ) sub
  WHERE
    rn = 1;
  </code></pre>
  <p>The next step would be to make a form to update the last state, and add rows with changeDate for the modified attributes.</p>
  <p>Maybe I overestimate my capabilities, but it's an example of small task I love to try myself for improvement or just out of curiosity and interest.</p>
  <p>EDIT : <a href="https://www.dropbox.com/scl/fo/jf5szst5glmfpb2yihyje/h?dl=0&amp;rlkey=yls7oxyi13mdix7dzl81yx6tu" rel="nofollow noreferrer">here</a> is a quick data sample</p>