Re: Update table with SQL

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")

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:e8xj3hcxGHA.560@xxxxxxxxxxxxxxxxxxxxxxx
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));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Roger Svensson" <roger.svensson@xxxxxxxxxxxxx> wrote in message
news:%23koZ3AcxGHA.4876@xxxxxxxxxxxxxxxxxxxxxxx

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))




.

Relevant Pages

  • Re: Lookup in Query
    ... UPDATE table1 ... I thought the "SET" line in my first query would ... "Tom Ellison" wrote: ... FROM table1, table2 ...
    (microsoft.public.access.queries)
  • Re: "Query must use updateable query" error message
    ... You won't be able to write this in Access SQL syntax. ... UPDATE table1 ... Trying to run the update query results in the "Query must use updateable ... INNER JOIN ((SELECT DISTINCT OrderNr, ...
    (microsoft.public.access.queries)
  • Update statement using multiple tables
    ... I have a question, hopefully an easy one, about updating values in a ... I would like to have a query ... I thought that if i put table2 in the update clause (update table1, ...
    (comp.databases.informix)
  • Re: Updating the first row which is null
    ... In your query, your WHERE clause is limiting your results to ... I am trying to update table1 and set a new value with a condition ... The query executes but it doesn t not update the informations. ... UPDATE table1 SET table1.Name = newName ...
    (comp.databases.ms-access)
  • Re: Update table with SQL
    ... Where can I get a good overview of those "hidden" Access functions? ... UPDATE TABLE1 ... I have a SQL query that works fine on an SQL server database but not on ...
    (microsoft.public.access.queries)