Access SQL Select most recent records out of a group with conditions

user_2994278 0
Viewed 45

I have two tables in MS Access 2016 x64. I want to update the field status_id in table transactions with the query results of table comments (field status_id), which must be the most recent status_id not being 0 or 6. No matter what I try, even using joins, the result keeps providing not the expected results where it (should be 9 records).

How to create a query which only selects the correct records?

The update SQL is quite simple and left out for clarity. If I have a working SELECT query I am able to change it to an update SQL.

Thx for your help!

Table transactions      
id      trans_no      status_id
12      9483241273    
13      9483241274    
14      9483241275    
15      9483241276    
16      9483241277    
17      9483241343    
18      9483241344    
19      9483241345    
20      9483241346    
21      9483390967   

Table comments
id      trans_id   com_date               status_id
29      12         23-06-2020 16:40:21    1
30      13         23-06-2020 16:40:26    1
31      14         23-06-2020 16:40:32    1
32      15         23-06-2020 16:40:38    1
33      16         23-06-2020 16:40:44    1
34      17         23-06-2020 16:40:50    1
35      18         23-06-2020 16:40:56    1
36      19         23-06-2020 16:41:01    1
37      20         23-06-2020 16:41:07    1
38      15         23-06-2020 16:40:44    6
39      18         25-06-2020 16:40:44    6
40      15         22-06-2020 16:40:38    11

SQL

SELECT comments.trans_id, comments.status_id, MAX(comments.com_date) AS com_date
FROM comments
GROUP BY comments.trans_id, comments.status_id, MAX(comments.com_date)
HAVING comments.status_id<>0 AND comments.status_id)<>6))

Result: 10 records (expected 9)

trans_id    status_id     com_date
12          1             23-06-2020 16:40:21
13          1             23-06-2020 16:40:26
14          1             23-06-2020 16:40:32
15          1             23-06-2020 16:40:38
15          11            22-06-2020 16:40:38
16          1             23-06-2020 16:40:44
17          1             23-06-2020 16:40:50
18          1             23-06-2020 16:40:56
19          1             23-06-2020 16:41:01
20          1             23-06-2020 16:41:07

SQL

SELECT a.trans_id, a.status_id  
FROM comments a
INNER JOIN
(SELECT b.trans_id, Max(b.com_date) AS com_date
FROM comments b
GROUP BY b.trans_id) c
ON a.trans_id=c.trans_id 
AND a.com_date = c.com_date
WHERE a.status_id<>0 AND a.status_id<>6

Result: 7 records (expected 9)

trans_id    status_id
12          1
13          1
14          1
16          1
17          1
19          1
20          1
ms-access sql
3 Answers

Any query that has the totals enabled becomes an un-editable query so this will cause some issues on your end. This also plays into why you are seeing the results you are.

Your first query with 10 results has 10 because there is a Group By on the status id and trans_id 15 has an entry for 1 and 11 causing both records to exist.

The second one has to do with the inner join due to the fact that the only omitted records are ones with multiple entries.

The way I would perform this update is to write a little VBA module to perform the update instead of trying to use queries.

user_137953780

An UPDATE action that involves an aggregate query will prevent UPDATE action. Will have to use alternate method to edit field. Saving calculated, especially aggregate, data is usually ill-advised. If you can calculate for UPDATE then you can calculate it when needed, as shown below:

SELECT comments.*
FROM comments 
WHERE (((comments.id) In (SELECT TOP 1 ID FROM comments AS Dupe WHERE Dupe.trans_id=comments.trans_id 
    AND Dupe.status_id NOT IN(0,6) ORDER BY Dupe.com_date DESC)));

Can LEFT JOIN that query to transactions to show all transaction records with the derived status code.

user_76071900

The final SQL is now:

UPDATE transactions AS upd LEFT JOIN 
(SELECT a.trans_id, a.status_id
FROM comments AS a 
WHERE (((a.id) IN (
SELECT TOP 1 ID FROM comments AS dupe 
WHERE dupe.trans_id=a.trans_id
AND dupe.status_id NOT IN (0,6) ORDER BY dupe.com_date DESC)))) AS b
ON upd.id = b.trans_id SET upd.status_id = b.status_id
WHERE (((b.trans_id) Is Not Null));
user_29942780
Related