CODEWITHSUNDEEP
sqlms-access
Amida
Amida

Reputation: 25

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.

list_of_dates MinMax dates sorted

#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```

Upvotes: 1

Views: 836

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

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)

Upvotes: 2

Ben McIntyre
Ben McIntyre

Reputation: 1968

It's a shot in the dark, but try adding DistinctRow as per SQL Update woes in MS Access - Operation must use an updateable query

Also try using an inner join. If you need to, you can run an update to a null value first for all the records in the query to simulate the effect of the outer join.

Upvotes: 0

Related Questions