Update table with SQL

[Roger Svensson]

Hi,

I have a SQL query that works fine on an SQL server database but not on an
equivalent Access database. Can anyone please help me tune the query to
function in Access?
The query looks like this:

update TABLE1 set Column1 = (select count(*) from TABLE2 where("Column2"=2))

/Roger

 

[Allen Browne]

Access will interpret the quotes as a literal string, so the where clause of
the query is comparing a literal string to a number.

Perhaps you want something like this:

update TABLE1 set Column1 =
(select count(*)
from TABLE2
where (Table2.Column2 = Table1.Column2));

 

[giorgio rancati]

Hi Roger,

see this Kb

 

[Roger Svensson]

I tried to type:
update TABLE1 set Column1 = (select count(*) from TABLE2
where([TABLE2].[Column2]=2))
Still without any success though...

 

[John Spencer]

How about using the DCount function? Access won't allow you to use
aggregate queries in an update query (other than in a where clause).

UPDATE TABLE1
SET Table1.Column1 =DCount("*","Table2","Column2 = 2")

 

[Roger Svensson]

Thanks John! You made my day...

Where can I get a good overview of those "hidden" Access functions? There
seems to be a lot of them...

/Roger