Question Regarding UPDATE and COUNT

[veggie216]

Hi,
I'm trying to update a table with a value coming from a COUNT function in ACCESS. What I need to do is count the number of 'passed' records in the 'ScriptTable' table (value = 'yes') and place that number into the 'EngineCodeName' table.
EngineCodeName has 44 different engine codes and ScriptTable has over 10000 entities but each entity has one of the engine codes as a field.

This is my code:

UPDATE
EngineCodeName SET EngineCodeName.Passed = (SELECT COUNT(ScriptTable.Passed)
FROM ScriptTable HAVING ScriptTable.Passed = YES and ScriptTable.EngineCode = 'ACP')
Where EngineCodeName.EngineCode = 'ACP';

Right now I have it for the EngineCode 'ACP' but what I really need to do is do this for all the different EngineCodes. I was trying something with joins and loops but to no avail.

Any help is appreciated. Thank you.

-Prady

 

[dodge20]

instead of EngineCode = 'ACP' use

EngineCode in ('ACP','BLAH','BLAH')

You can input as many as you need with IN

Dodge20

 

[veggie216]

Just to clarify, my first issue is getting the code to work and my second issue is using it for multiple enginecodes.

 

[dodge20]

Having is only to be used after a group by

if you change it to where, it probably will work.

Dodge20

 

[veggie216]

When I try that I get this message:
'Operation must use an updatable query'.

Is thie query even possible?

 

[dodge20]

You may have to do a count first, then update the field to that number. I guess I just assumed that you could update a field = to count, but the more I think about it, it doesn't make much sense to do that.

So if you just do this query

SELECT COUNT(ScriptTable.Passed)
FROM ScriptTable where ScriptTable.Passed = 'YES' and ScriptTable.EngineCode = 'ACP'

then do your update

UPDATE
EngineCodeName SET EngineCodeName.Passed = '####'
Where EngineCodeName.EngineCode = 'ACP'


Dodge20