How can I replace the LAST() function in MS Access with proper ordering on a rather large table?

Question

I have an MS Access database with the two tables, Asset and Transaction. The schema looks like this:

Table ASSET
Key     Date1       AType       FieldB      FieldC ...
A       2023.01.01  T1
B       2022.01.01  T1
C       2023.01.01  T2          
.
.


TABLE TRANSACTION
Date2       Key     TType1  TType2  TType3  FieldOfInterest ...
2022.05.31  A       1       1       1       10
2022.08.31  A       1       1       1       40
2022.08.31  A       1       2       1       41
2022.09.31  A       1       1       1       30
2022.07.31  A       1       1       1       30
2022.06.31  A       1       1       1       20
2022.10.31  A       1       1       1       45
2022.12.31  A       2       1       1       50
2022.11.31  A       1       2       1       47  
2022.05.23  B       2       1       1       30
2022.05.01  B       1       1       1       10
2022.05.12  B       1       2       1       20  
.
.
.

The ASSET table has a PK (Key). The TRANSACTION table has a composite key that is (Key, Date2, Type1, Type2, Type3).

Given the above tables let's see an example:

Input1 = 2022.04.01
Input2 = 2022.08.31

Desired result:
Key     FieldOfInterest
A       41

because if the Transactions in scope was to be ordered by Date2, TType1, TType2, TType3 all ascending then the record having FieldOfInterest = 41 would be the last one. Note that Asset B is not in scope due to Asset.Date1 < Input1, neither is Asset C because AType != T1. Ultimately I am curious about the SUM(FieldOfInterest) of all the last transactions belonging to an Asset that is in scope determined by the input variables.

The following query has so far provided the right results but after upgrading to a newer MS Access version, the LAST() operation is no longer reliably returning the row which is the latest addition to the Transaction table.

I have several input values but the most important ones are two dates, lets call them InputDate1 and InputDate2.

This is how it worked so far:

SELECT Asset.AType, Last(FieldOfInterest) AS CurrentValue ,Asset.Key  
FROM Transaction
INNER JOIN Asset ON Transaction.Key = Asset.Key 
WHERE Transaction.Date2 <= InputDate2 And Asset.Date1 >= InputDate1
GROUP BY Asset.Key, Asset.AType
HAVING Asset.AType='T1'

It is known that the grouped records are not guaranteed to be in any order. Obviously it is a mistake to rely on the order of the records of the group by operation will always keep the original table order but lets just ignore this for now.

I have been struggling to come up with the right way to do the following:

• join the Asset and Transaction tables on Asset.Key = Transaction.Key
• filter by Asset.Date1 >= InputDate1 AND Transaction.Date2 <= InputDate2
• then I need to select one record for all Transaction.Key where Date2 and TType1 and TType2 and TType3 has the highest value. (this represents the actual last record for given Key)

As far as I know there is no way to order records within a group by clause which is unfortunate.

I have tried Ranking, but the Transactions table is large (800k rows) and the performance was very slow, I need something faster than this. The following are an example of three saved queries that I wrote and chained together but the performance is very disappointing probably due to the ranking step.

-- Saved query step_1
SELECT Asset.*, Transaction.*
FROM Transaction
    INNER JOIN Asset ON Transaction.Key = Asset.Key
        WHERE Transaction.Date2 <= 44926 
        AND Asset.Date1 >= 44562 
        AND Asset.aType = 'T1'

-- Saved query step_2
SELECT tr.FieldOfInterest, (SELECT Count(*) FROM
       (SELECT tr2.Transaction.Key, tr2.Date2, tr2.Transaction.tType1, tr2.tType2, tr2.tType3 FROM step_1 AS tr2) AS tr1
   WHERE (tr1.Date2 > tr.Date2 OR 
         (tr1.Date2 = tr.Date2 AND tr1.tType1 > tr.Transaction.tType1) OR
         (tr1.Date2 = tr.Date2 AND tr1.tType1 = tr.Transaction.tType1 AND tr1.tType2 > tr.tType2) OR
         (tr1.Date2 = tr.Date2 AND tr1.tType1 = tr.Transaction.tType1 AND tr1.tType2 = tr.tType2 AND tr1.tType3 > tr.tType3))
         AND tr1.Key = tr.Transaction.Key)+1 AS Rank
FROM step_1 AS tr

-- Saved query step_3
SELECT SUM(FieldOfInterest) FROM step_2
WHERE Rank = 1

I hope I am being clear enough so that I can get some useful recommendations. I've been stuck with this for weeks now and really don't know what to do about it. I am open for any suggestions.

Answer 1

Reading the following specification

then I need to select one record for all Transaction.Key where Date2 and TType1 and TType2 and TType3 has the highest value. (this represents the actual last record for given Key)

Consider a simple aggregation for step 2 to retrieve the max values then in step 3 join all fields to first query.

Step 1 (rewritten to avoid name collision and too many columns)

SELECT a.[Key] AS Asset_Key, a.Date1, a.AType,
       t.[Key] AS Transaction_Key, t.Date2,
       t.TType1, t.TType2, t.TType3, t.FieldOfInterest
FROM Transaction t
INNER JOIN Asset a ON a.[Key] = a.[Key]
WHERE t.Date2 <= 44926 
  AND a.Date1 >= 44562 
  AND a.AType = 'T1'

Step 2

SELECT Transaction_Key, 
       MAX(Date2) AS Max_Date2,
       MAX(TType1) AS TType1,
       MAX(TType2) AS TType2,
       MAX(TType3) AS TType3
FROM step_1
GROUP Transaction_Key

Step 3

SELECT s1.*
FROM step_1 s1
INNER JOIN step_2 s2
  ON  s1.Transaction_Key = s2.Transaction_Key
  AND s1.Date2  = s2.Max_Date2
  AND s1.TType1 = s2.Max_TType1
  AND s1.TType2 = s2.Max_TType2
  AND s1.TType3 = s2.Max_TType3