Nested aggregates update query

[misterjib]

Hi Everyone.

I've binned this approach now because I couldn't get it working and instead used a two step process (which I didn't really want/couldn't be bothered to do) where I first create a temp table to get the COUNT of the DISTINCT IDs then UPDATE the target table with a JOIN on the temp.

However, I am curious if it is actually possible to do this in one SQL command... I reckon I could probably get this working in MSSQL server which I am more familiar with but clearly I'm inept with Access!

Forgive the wordy alias, I struggle with abstract logic at the best of times.

The SQL:

UPDATE EQAS
SET EQAS.ptcount = targIDCnt.distinctids_count
FROM (SELECT Count(dIDs.distinctids) AS distinctIDs_count,
dIDs.targetinr,
dIDs.pracid
FROM (SELECT DISTINCT ( [reviews].[id] ) AS distinctIDs,
reviews.targetinr,
reviews.pracid
FROM reviews
WHERE reviews.pracid = 1) AS dIDs
GROUP BY dIDs.targetinr,
dIDs.pracid) AS targIDCnt
INNER JOIN eqa_statistics AS EQAS
ON targIDcnt.targetinr = EQAS.targetinr
AND targIDcnt.pracid = EQAS.practiceid;

 

[dhookom]

I doubt this can be accomplished in one Access query. MS SQL Server is much more accommodating when it comes to allowing updates with SQL containing aggregates. There are times when Access programmers have to fall back to using domain aggregate functions :-(

Duane
Hook'D on Access
MS Access MVP

 

[misterjib]

Thanks for the feedback dhookom. I've not used domain aggregate functions much - I shall investigate for future reference.

misterjib.