Update Count Problems

[nsl100]

Hello All,

I have recently discovered that I cannot UPDATE a table with the results of a conventional COUNT query. I have struggled all day to find a work around for this problem but to no avail. Outlined below is what I'm attempting to do in 'conventional' SQL.

UPDATE tableZ
SET columnA = (SELECT COUNT (*) AS columnA
FROM tableX
WHERE tableX.colB = 1 AND tableX.colC = 2);

The tables (Z and X) are not linked to any other tables and are empty (although theuy have structure). I have full read write permissions on the database and all its tables.

Would anoyone be able to offer adivce on how I could transform this UPDATE query into something Access (2003) would find acceptable?

 

[traingamer]

The tables (Z and X) are not linked to any other tables and are empty

If they are empty you have no records to update.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[nsl100]

Apolgies. By empty I meant that the database contained a couple of rows with null (zeros) values. I have just added some values into the rows and attempted the query. Access still doesn't like the query being run and hands me a 'Operation must use an updateable query' message.

 

[nsl100]

Fixed - having poked around these forums and elsewhere I have solved my problem - see below:

UPDATE table Z
SET columnA = DCount("*", "tableX", "colB = 1 and colC = 2");

In addition I included some data in tableZ to ensure that it could be updated.