A Simple Query problem?

[Ian Chappel]

I've wrestled with this one, but can't quite manage it!

I have a tblStatement containing fields StatementID, Date, Description, and
Value. For all records with Description = "NATWEST BANK", there are two
values for each particular date. What I want to do is update the Description
depending on whether the value is the Max or Min value for that particular
date.

I can use criteria with GroupBy and Min/Max in the query grid, but obviously
cannot update the Description field as it is a GroupBy.

 

[Michel Walsh]

And you won't be able to update anything if you use a GROUP BY or an
aggregate in the main query, elsewhere than in a sub-query in the WHERE main
clause.


UPDATE tableName
SET description = 'hello'
WHERE description='NATWEST BANK'
AND theValue=(SELECT MAX(b.theValue)
FROM tableName AS b
WHERE b.description = description
AND b.date = date)



The sub-query looks for records having the same 'actual' date and
description that the record which is a candidate to the update, and pump out
the maximum theValue value. Back in the main clause, if that value match the
one returned by the subquery, and the description is the one we target, then
the update is performed.

Since we refer to the same table twice, but with different meaning, at least
one of the reference has to be aliased. I decided to alias the one in the
subquery, and personally, I prefer single letter alias. So 'b' is like
running independently over all records of the original table, but we have to
keep only those records who 'fit' the constraint (of the WHERE clause).
b.description = description is thus read as a restriction on 'b', it must
have the same description than the one of the record we will eventually
update.




Vanderghast, Access MVP

 

[Ian Chappel]

Thanks for your swift reply, Michel.

This makes sense to me, but unfortunately returns/updates 0 records! I'm not
sure what's wrong, and have played around with it to no avail.

A sample of tblStatement is:

StatementID BankID Date Type Description Value
1327 2 13-Mar-07 D/D NATWEST BANK -£448.48
1328 2 13-Mar-07 D/D NATWEST BANK -£432.65
1329 2 13-Mar-07 BAC P302223507 £2,000.00
1330 1 15-Mar-07 CHQ 009070 -£293.75
1331 2 15-Mar-07 D/D POWERGEN -£43.00
1352 2 13-Apr-07 D/D NATWEST BANK -£432.65
1353 2 13-Apr-07 D/D NATWEST BANK -£448.48
1354 2 13-Apr-07 D/D NICO DIRECT DEBIT -£8.40

and the amended SQL is:

UPDATE tblStatement SET description = 'NATWEST BANK 16'
WHERE Description='NATWEST BANK'
AND Value=(SELECT MAX(b.Value)
FROM tblStatement AS b
WHERE b.Description = Description
AND b.Date = Date);

Records 1328 and 1352 should be updated, shouldn't they?

 

[Michel Walsh]

It seems that we *have to* alias the first reference too (which remove any
ambiguity, so it is not a bad idea in the first place):


UPDATE tblStatement AS a SET description = 'NATWEST BANK 16'
WHERE Description='NATWEST BANK'
AND Value=(SELECT MAX(b.Value)
FROM tblStatement AS b
WHERE b.Description = a.Description
AND b.Date = a.Date);




Vanderghast, Access MVP

 

[Ian Chappel]

Thanks Michel, works perfectly 1st time.

It seems that we *have to* alias the first reference too (which remove any
ambiguity, so it is not a bad idea in the first place):


UPDATE tblStatement AS a SET description = 'NATWEST BANK 16'
WHERE Description='NATWEST BANK'
AND Value=(SELECT MAX(b.Value)
FROM tblStatement AS b
WHERE b.Description = a.Description
AND b.Date = a.Date);




Vanderghast, Access MVP