ACCESS - Select rows on first occurrence of each unique value

Question

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

Let's imagine I got 3 tables like this

PROJECT_TABLE

+------------+--------------+
| PROJECT_ID | Project_Date |
+------------+--------------+
| Project01  | 01/01/2022   |
+------------+--------------+
| Project02  | 16/02/2020   |
+------------+--------------+
| Project03  | 19/03/2021   |
+------------+--------------+
| Project04  | 01/01/2022   |
+------------+--------------+

ACTIVITY_TABLE

+-------------+------------+
| ACTIVITY_ID | PROJECT_ID |
+-------------+------------+
| Activity01  | Project01  |
+-------------+------------+
| Activity02  | Project02  |
+-------------+------------+
| Activity03  | Project01  |
+-------------+------------+
| Activity04  | Project03  |
+-------------+------------+
| Activity05  | Project04  |
+-------------+------------+
| Activity06  | Project01  |
+-------------+------------+
| Activity07  | Project04  |
+-------------+------------+
| Activity08  | Project03  |
+-------------+------------+

ITEMS_TABLE

+---------+-------------+
| ITEM_ID | ACTIVITY_ID |
+---------+-------------+
| Item01  | Activity01  |
+---------+-------------+
| Item02  | Activity06  |
+---------+-------------+
| Item03  | Activity03  |
+---------+-------------+
| Item01  | Activity05  |
+---------+-------------+
| Item03  | Activity07  |
+---------+-------------+
| Item04  | Activity02  |
+---------+-------------+
| Item02  | Activity04  |
+---------+-------------+
| Item01  | Activity08  |
+---------+-------------+

Consolidated extract of last done for each activity that I need to get will look like this

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

The problem will be the or statement in this table, I need to get any of the two possible data.

At this moment I can get this

+---------+------------------+
| ITEM_ID | MaxDatePerformed |
+---------+------------------+
| Item01  | 01/01/2022       |
+---------+------------------+
| Item02  | 01/01/2022       |
+---------+------------------+
| Item03  | 01/01/2022       |
+---------+------------------+
| Item04  | 16/02/2020       |
+---------+------------------+

With the following query

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

But when I try to add ",ACTIVITY_ID" to this query I get "Your query doesn't include ACTIVITY_ID as aggregate function"

I know that a solution is existing for MySQL (MySQL Select rows on first occurrence of each unique value) but I can't find any for ACCESS, it seems that this "cheat" doesn't exist.

June7 · Answer 1 · 2022-08-04T04:27:50.910

Consider:

Query1:

SELECT Items_Table.Item_ID, Activity_Table.Activity_ID, Project_Table.Project_ID, Project_Table.Project_Date
FROM Project_Table 
RIGHT JOIN (Items_Table RIGHT JOIN Activity_Table ON Items_Table.Activity_ID = Activity_Table.Activity_ID) 
ON Project_Table.Project_ID = Activity_Table.Project_ID;

Query2:

SELECT Item_ID, Activity_ID, Project_ID, Project_Date 
FROM Query1 WHERE Activity_ID IN
(SELECT TOP 1 Activity_ID FROM Query1 AS Dupe
 WHERE Dupe.Item_ID = Query1.Item_ID
 ORDER BY Dupe.Project_Date DESC, Dupe.Activity_ID)
ORDER BY Item_ID;

Then try modification Dupe.Activity_ID DESC and see what happens.

For more info review http://allenbrowne.com/subquery-01.html

ACCESS - Select rows on first occurrence of each unique value

1 Answers1