Link to home
Start Free TrialLog in
Avatar of ctnovelist
ctnovelist

asked on

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!
Avatar of deadlyDev
deadlyDev
Flag of Spain image

Try this out...
UPDATE MPD
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


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

Avatar of Scott McDaniel (EE MVE )
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?

Avatar of ctnovelist
ctnovelist

ASKER

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.

Thanks!
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;
<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.
Hi LSM:

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

Thanks!
Can you upload your database here?
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?
ASKER CERTIFIED SOLUTION
Avatar of ctnovelist
ctnovelist

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.