Ms Access/ SQL query

Question

I have a table as below. Need a Query to select the Last activity of the User. (There should not be multiple listing for same user, Single user single row)

+----------------+---------+--------------------+-----------------+------------+
|      Date      | BOT_ID  |      Process       |  LastModified   |   Status   |
+----------------+---------+--------------------+-----------------+------------+
| 17/09/26 00:00 | User 1  | Interaction record | 9/19/2017 10:50 | In Process |
| 17/09/26 00:00 | User 2  | Single Assessed    | 9/26/2017 12:00 | In process |
| 17/09/26 00:00 | User 3  | Interaction record | 9/26/2017 11:35 | Completed  |
| 17/09/26 00:00 | User 4  | Metering           | 9/26/2017 11:50 | In proce   |
| 17/09/26 00:00 | User 1  | Move In            | 9/26/2017 11:50 | In process |
| 17/09/26 00:00 | User 4  | Interaction record | 9/26/2017 11:58 | Completed  |
| 17/09/26 00:00 | User 5  | Direct Debit       | 9/26/2017 11:10 | Completed  |
| 17/09/26 00:00 | User 17 | latest             | 9/26/2017 0:15  | In Process |
+----------------+---------+--------------------+-----------------+------------+

Is your question about Access or MySQL? Please only use the appropriate tag. — Barmar, Sep 26 '17 at 07:29
In MySQL it would be: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 Maybe it's similar in Access. — Barmar, Sep 26 '17 at 07:31
pls next time avoid using images and use text as above edited question — etsa, Sep 26 '17 at 07:34
read introduction to SO. Doing this effort help us to help you. — etsa, Sep 26 '17 at 07:45

etsa · Accepted Answer · 2017-09-26T07:58:07.590

1

You can try this query. The subselect calculate max(lastmodified) for each user, using GROUP BY. The result is joined to main table using BOT_ID and the calculated date with lastmodified date. If a user has two same date lastmodified, both the rows will be returned:

SELECT A.* 
FROM YOURTABLE A
INNER JOIN (SELECT BOT_ID, MAX(LASTMODIFIED) AS LAST_ACTIVITY 
            FROM YOURTABLE 
            GROUP BY BOT_ID) B ON A.BOT_ID=B.BOT_ID AND A.LASTMODIFIED = B.LAST_ACTIVITY

edited Sep 26 '17 at 07:58

answered Sep 26 '17 at 07:42

etsa

5,020
1
7
18

Yeai.. that worked. Thanks esta. Small modification, used Last() instead of max() – Subash Sukumari Sep 26 '17 at 08:41

Gustav · Answer 2 · 2017-09-26T10:00:05.133

0

You can use:

Select Max([Last Modified]) As [Last Activity] 
From YourTable 
Where BOT_ID = "User 1"

To list all:

Select BOT_ID, Max([Last Modified]) As [Last Activity] 
From YourTable 
Group By BOT_ID

edited Sep 26 '17 at 10:00

answered Sep 26 '17 at 07:37

Gustav

53,498
7
29
55

But, I need Last activity for Each User listed. – Subash Sukumari Sep 26 '17 at 07:42
Well, see extended answer. – Gustav Sep 26 '17 at 10:00

Ms Access/ SQL query

2 Answers2