title: ACCESS - Select rows on first occurrence of each unique value
body: <p>I got an ACCESS database related issue on which I already spend too much time without getting any hope to find a solution by myself</p>
  <p>Let's imagine I got 3 tables like this</p>
  <p>PROJECT_TABLE</p>
  <pre><code>+------------+--------------+
  | PROJECT_ID | Project_Date |
  +------------+--------------+
  | Project01  | 01/01/2022   |
  +------------+--------------+
  | Project02  | 16/02/2020   |
  +------------+--------------+
  | Project03  | 19/03/2021   |
  +------------+--------------+
  | Project04  | 01/01/2022   |
  +------------+--------------+
  </code></pre>
  <p>ACTIVITY_TABLE</p>
  <pre><code>+-------------+------------+
  | ACTIVITY_ID | PROJECT_ID |
  +-------------+------------+
  | Activity01  | Project01  |
  +-------------+------------+
  | Activity02  | Project02  |
  +-------------+------------+
  | Activity03  | Project01  |
  +-------------+------------+
  | Activity04  | Project03  |
  +-------------+------------+
  | Activity05  | Project04  |
  +-------------+------------+
  | Activity06  | Project01  |
  +-------------+------------+
  | Activity07  | Project04  |
  +-------------+------------+
  | Activity08  | Project03  |
  +-------------+------------+
  </code></pre>
  <p>ITEMS_TABLE</p>
  <pre><code>+---------+-------------+
  | ITEM_ID | ACTIVITY_ID |
  +---------+-------------+
  | Item01  | Activity01  |
  +---------+-------------+
  | Item02  | Activity06  |
  +---------+-------------+
  | Item03  | Activity03  |
  +---------+-------------+
  | Item01  | Activity05  |
  +---------+-------------+
  | Item03  | Activity07  |
  +---------+-------------+
  | Item04  | Activity02  |
  +---------+-------------+
  | Item02  | Activity04  |
  +---------+-------------+
  | Item01  | Activity08  |
  +---------+-------------+
  </code></pre>
  <p>Consolidated extract of last done for each activity that I need to get will look like this</p>
  <pre><code>+---------+-------------+------------+--------------+
  | ITEM_ID | ACTIVITY_ID | PROJECT_ID | Project_Date |
  +---------+-------------+------------+--------------+
  | Item01  | Activity01  | Project01  | 01/01/2022   |
  |         | or          | or         |              |
  |         | Activity05  | Project04  |              |
  +---------+-------------+------------+--------------+
  | Item02  | Activity06  | Project01  | 01/01/2022   |
  +---------+-------------+------------+--------------+
  | Item03  | Activity03  | Project01  | 01/01/2022   |
  |         | or          | or         |              |
  |         | Activity07  | Project04  |              |
  +---------+-------------+------------+--------------+
  | Item04  | Activity02  | Project02  | 16/02/2020   |
  +---------+-------------+------------+--------------+
  </code></pre>
  <p>The problem will be the <em>or</em> statement in this table, I need to get any of the two possible data.</p>
  <p>At this moment I can get this</p>
  <pre><code>+---------+------------------+
  | ITEM_ID | MaxDatePerformed |
  +---------+------------------+
  | Item01  | 01/01/2022       |
  +---------+------------------+
  | Item02  | 01/01/2022       |
  +---------+------------------+
  | Item03  | 01/01/2022       |
  +---------+------------------+
  | Item04  | 16/02/2020       |
  +---------+------------------+
  </code></pre>
  <p>With the following query</p>
  <pre><code>SELECT ITEM_ID
      ,Max(Project_Date) AS MaxDatePerformed
  FROM (
      ITEMS_TABLE INNER JOIN (
          ACTIVITY_TABLE INNER JOIN PROJECT_TABLE ON ACTIVITY_TABLE.PROJECT_ID = PROJECT_TABLE.PROJECT_ID
          ) ON ITEMS_TABLE.ACTIVITY_ID = ACTIVITY_TABLE.ACTIVITY_ID
      ) AS [ACTIVITY_DATE_INFO]
  GROUP BY [ACTIVITY_DATE_INFO].ITEM_ID
  </code></pre>
  <p>But when I try to add <em>&quot;,ACTIVITY_ID&quot;</em> to this query I get <em>&quot;Your query doesn't include ACTIVITY_ID as aggregate function&quot;</em></p>
  <p>I know that a solution is existing for MySQL (<a href="https://stackoverflow.com/questions/12065931/mysql-select-rows-on-first-occurrence-of-each-unique-value">MySQL Select rows on first occurrence of each unique value</a>) but I can't find any for ACCESS, it seems that this &quot;cheat&quot; doesn't exist.</p>