Update table based on values from a query

Hello everyone.  I can't believe that this problem is kicking me like it is, as this seems like something that ought to have taken about 10 minutes to complete.  It seems Access always finds a way to make something simple complicated.

I'm trying to update a field in a table with values from a query.  The SQL code is currently as follows:

UPDATE [Master Parts Data] INNER JOIN CGT3_11_MinServiceStockByPN ON [Master Parts Data].[P/N]=CGT3_11_MinServiceStockByPN.ServicePart SET [Master Parts Data].MinStock = CGT3_11_MinServiceStockByPN.SumOfMinStock
WHERE [Master Parts Data].AutoMinStockOverride=No;

With this, I'm getting an "Operation must use an updateable query" error.  I attempted to use a DLookup version of this, something like:

UPDATE [Master Parts Data] SET MinStock = DLookUp("SumOfMinStock","CGT3_11_MinServiceStockByPN","ServicePart='Me.[P/N]'")
WHERE AutoMinStockOverride = No;

...but this doesn't work.  Specifically, it just doesn't update anything (but throws no errors).  I'm pretty sure the problem lies in the "WHERE" clause of the DLookUp itself, but I'm not sure how else to state it.  I've tried it as [Master Parts Data]![P/N] and simply [P/N] with the same results.  I've also left out the statement entirely, which of course resulted in every record being updated with the first record returned from the DLookUp statement.

Any help would be greatly appreciated.  Thanks!
Who is Participating?
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.

Try this out...
SET MPD.MinStock=MSS.SumOfMinStock
FROM [Master Parts Data] MPD
INNER JOIN CGT3_11_MinServiceStockByPN MSS
	ON MPD.[P/N]=MSS.ServicePart
WHERE [Master Parts Data].AutoMinStockOverride='No';

Open in new window

DimitrisSenior Solution ArchitectCommented:

UPDATE [Master Parts Data] 
SET [Master Parts Data].MinStock = CGT3_11_MinServiceStockByPN.SumOfMinStock
FROM [Master Parts Data] 
     INNER JOIN CGT3_11_MinServiceStockByPN 
         ON [Master Parts Data].[P/N]=CGT3_11_MinServiceStockByPN.ServicePart 
WHERE [Master Parts Data].AutoMinStockOverride="No"

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is your data stored on a Server (i.e. SQL Server or such), or in an Access/Jet database? If so, then the suggestions by the other Experts are more server-standard (i.e. T-SQL specific, in the case of SQL Server) and won't work with Jet. The syntax is a bit odd, but yours is the correct construct IF you're using Jet-based data and running this in your Access app (i.e. this isn't a Stored Procedure on a server, or something of that nature).

Does CG3T_11_MinServiceStockByPN.SumOfMinStock show valid data, and show it as expected (that is, the sums are correct for the P/N)?

Does CG3T_11_MinServiceStockByPN.SumOfMinStock include the correct P/N in the ServicePart field for the Summed data?

Is AutoMinStockOverride a Boolean field?

Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ctnovelistAuthor Commented:
This is in an Access, Jet-based database (not SQL Server using stored procedures).  The two suggestions above give a syntax error (operator missing) and seems to point to the FROM clause, so your explanation is probably correct.

The CGT3_11_MinServiceStockByPN is a query that opens and displays data correctly.

AutoMinStockOverride is a Yes/No (Boolean) field in Access.

I never realized saving an UPDATE query in Access would be such a pain.  This is ultimately to be used in a form anyway, so if it comes down to it, I'm sure I can make the query work through the form's VBA code.

If anyone knows what will fix this, though, I would still use it.  And, at any rate, I'm now very curious what Access WILL accept for this type of query, if anything.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try use False instead of "No".

If you build a simple SELECT with your Join, does it return the records you expect?

SELECT MinStock FROM [Master Parts Data] INNER JOIN CGT3_11_MinServiceStockByPN ON [Master Parts Data].[P/N]=CGT3_11_MinServiceStockByPN.ServicePart
WHERE [Master Parts Data].AutoMinStockOverride=False;
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<I never realized saving an UPDATE query in Access would be such a pain. >

It's the Join that's getting you ... Update queries are simple, but there are many things that can go wrong when you begin using Joins in them.
ctnovelistAuthor Commented:

Changing No to False did not result in any difference.
Yes, the query does work when I change it to a Select query.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you upload your database here?
ctnovelistAuthor Commented:
Hmm, not sure about uploading the whole thing.  It's about 54MB and contains info proprietary to our business.  What are you looking for exactly?
ctnovelistAuthor Commented:
At this piont, I assume that no one has a real solution to this problem.  I appreciate those of you who took the time to look into it and am sorry for having presented something that is apparently not possible in Access.

What I'm trying to do with this UPDATE query can be accomplished effectively using VBA code in the form I'm going to generate.  Therefore, I will abandon the standalone query and pursue this route.

Thanks again.

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It IS possible to do, but without knowing your exact table structure it's difficult to advise. I regularly do UPDATES with JOINS in them. Somewhere along the line, your query is not returning the correct data, or you're trying to work with the wrong side of a One to Many join, perhaps.
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.