Re: Update table with SQL

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

"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:eFLHzpexGHA.2264@xxxxxxxxxxxxxxxxxxxxxxx
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: Update table with SQL
    ... How about using the DCount function? ... aggregate queries in an update query. ... UPDATE TABLE1 ... I have a SQL query that works fine on an SQL server database but not on ...
    (microsoft.public.access.queries)
  • Re: Prompting for parameters in MSQuery to pass into a stored procedure
    ... flat files on a SQL Server database. ... I'm trying to create a complicated query in MSQuery which prompts the ... extracts the data from flat files on an SQL server database via ODBC. ... labels to my parameter prompts (instead of just Parameter 1, ...
    (microsoft.public.excel)
  • Re: Please help!! How to get parameter back in ADO.NET
    ... Bill Vaughn might spank you for using command(don't ... > Hello Bill, ... >> If you aren't using a SP, you need to query the newly created ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Filtering DB Call based on local Data
    ... SQL Server will take care of the rest. ... single parameter to your query (it can be a stored procedure or dynamic ... specific to the order from the order table on a Sql Server Database ... filter the set. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Changing "External Data" DSN Details to point to different server
    ... > from a SQL Server database. ... > If the Microsoft Query had been saved ... > with Notepad to correct the DSN it's using. ... > I change the DSN when it is embedded in the spreadsheet? ...
    (microsoft.public.excel.misc)
Loading