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.
LVL 5
usslindstromAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GRayLCommented:
How about:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.