• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Run an update query based off results of a select query in access

Experts,

I'm attempting to update a table after making an update query.  The update works perfect when it's applied to all records.  But - I need to end up updating -only- the records that come up from another SELECT query I've created.

Whenever I join the two tables, and try to run the update query though - I get pinged with this error:  "Operation must use an updateable query." - Which it's all because' the select query isn't editable.

Can somebody help me out - in showing me what statement I would need to be able to pull this off?

Here's my update query string:

UPDATE dbo_computer INNER JOIN FilteredAbvNameIAVARequired ON dbo_computer.name=FilteredAbvNameIAVARequired.AbvName SET dbo_computer.group_id = 11;

And the select string I need to have the records get updated from:

SELECT AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded
FROM AbvNameIAVARequired
GROUP BY AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded
HAVING (((AbvNameIAVARequired.IAVANeeded)=Forms![IAVA Dashboard]!IAVAMenu));




Thanks for always helping me out.
0
usslindstrom
Asked:
usslindstrom
  • 4
  • 3
  • 2
  • +1
1 Solution
 
tigin44Commented:
use this
UPDATE
SET dbo_computer.group_id = 11
from dbo_computer
      INNER JOIN (SELECT AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded
            FROM AbvNameIAVARequired
            GROUP BY AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded
            HAVING (((AbvNameIAVARequired.IAVANeeded)=Forms![IAVA Dashboard]!IAVAMenu))
            ) ON dbo_computer.name=FilteredAbvNameIAVARequired.AbvName ;
0
 
usslindstromAuthor Commented:
Thank you very much for that code.  I think it will be exactly what I need.  :)

Unfortunately - I get a couple of errors when I try to run it...

When I copy/paste that code into the update query, I get "Syntax Error in Update Statement" - and it highlights "SET" in the second line (immediately after UPDATE)

When I copy/paste that code into a button on the form I'm using, I get "Sub or Function not Defined"

Sorry for my basic understanding of SQL and VBA type stuff - but could you please walk me through on how to implement your code here?

Thanks.
0
 
peter57rCommented:
You will not be able to create an update query in Access/JET SQL that uses a GroupBy or summary function anywhere in its sql.  Access/JET will always treat it as non-updateable.

Since you are linking to sqlserver you might consider using a passthrough query (which would be written in TSQL) and so might overcome the JET SQL limitation.

To do this type of update using an ACcess query you would have to create a (temporary?)  table from your
group-by query and you can then update your computer table with data from the temp table.


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
GRayLCommented:
How about:

SELECT AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded
FROM AbvNameIAVARequired
WHERE (((AbvNameIAVARequired.IAVANeeded)=Forms![IAVA Dashboard]!IAVAMenu))
ORDER BY AbvNameIAVARequired.AbvName, AbvNameIAVARequired.IAVANeeded;

0
 
usslindstromAuthor Commented:
If updating a SQL table isn't possible, on how I'm trying to do it here...

Can somebody walk me through what I need to do, in order to use that "temp table" idea from Peter57r?

Thanks for helping me.
0
 
GRayLCommented:
Did you try what I had proposed?  By changing the GROUP  BY to a ORDER BY and putting the HAVING into the WHERE clause I thought the query became updatable.  As far as I could tell it should return the same recordset.
0
 
usslindstromAuthor Commented:
I aplogize - GRayL - the db is at work ~ so I should have a chance to try your solution 2morrow...  Thanks for the ideas.

I`m just trying to get as much information on this as possible, cause my time to resolution at work is pretty small - and I`m willing to try anything to get it working.  :)

I made references to Peter57r`s post - cause he said what I was trying to do wasn`t possible...  So any more info I can get, the better.  :)

I will definately try your solution on Monday though when I have access to the db again.

0
 
peter57rCommented:
Yes, Ray did a bit more work than the rest of us and saw that the summary query wasn't really doing anything significant - just reducing the number of updates.  
0
 
usslindstromAuthor Commented:
Thank you very much for this!  :)

Worked like a champ here.  The project this applies to is now complete.  It's much appreciated!

I take it - my error - was instead of having a WHERE clause, I was simply trying to group the results.

Hmmm.  Live and learn!   :)    Thanks for the solution here.
0
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now