Access SQL: Update list with Max() and Min() values not possible

I’ve a list of dates: list_of_dates.

I want to find the max and min values of each number with this code (#1).

It works how it should, and therefore I get the table MinMax

Now I want to update a other list (list_of_things) with these newly acquired values (#2).

However, it is not possible. I assume it’s due to DISTINCT and the fact that I always get two rows per number, each with the min and max values. Therefore an update is not possible. Unfortunately I don’t know any other way.

#1
SELECT a.number, b.MaxDateTime, c.MinDateTime
FROM (list_of_dates AS a 

INNER JOIN (
SELECT a.number, MAX(a.dat) AS MaxDateTime 
FROM list_of_dates AS a 
GROUP BY a.number) AS b 
ON a.number = b.number) 

INNER JOIN (SELECT a.number, MIN(a.dat) AS MinDateTime 
FROM list_of_dates AS a 
GROUP BY a.number) AS c 
ON a.number = c.number;


#2
UPDATE list_of_things AS a 
LEFT JOIN MinMax AS b 
ON a.number = b.number 
SET a.latest = b. MaxDateTime, a.ealiest = b.MinDateTime```

Answer

No part of an MS Access update query can contain aggregation, else the resulting recordset becomes ‘not updateable’.

In your case, the use of the min & max aggregate functions within the MinMax subquery cause the final update query to become not updateable.

Whilst it is not always advisable to store aggregated data (in favour of generating an output from transactional data using queries), if you really need to do this, here are two possible methods:

1. Using a Temporary Table to store the Aggregated Result

Run a select into query such as the following:

select 
    t.number, 
    max(t.dat) as maxdatetime,
    min(t.dat) as mindatetime 
into 
    temptable
from 
    list_of_dates t
group by 
    t.number

To generate a temporary table called temptable, then run the following update query which sources date from this temporary table:

update 
    list_of_things t1 inner join temptable t2
    on t1.number = t2.number
set 
    t1.latest = t2.maxdatetime,
    t1.earliest = t2.mindatetime

2. Use Domain Aggregate Functions

Since domain aggregate functions (dcount, dsum, dmin, dmax etc.) are evaluated separately from the evaluation of the query, they do not break the updateable nature of a query.

As such, you might consider using a query such as:

update 
    list_of_things t1
set 
    t1.latest = dmax("dat","list_of_dates","number = " & t1.number),
    t1.earliest = dmin("dat","list_of_dates","number = " & t1.number)