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_MinServiceStockByP N ON [Master Parts Data].[P/N]=CGT3_11_MinSer viceStockB yPN.Servic ePart SET [Master Parts Data].MinStock = CGT3_11_MinServiceStockByP N.SumOfMin Stock
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","C GT3_11_Min ServiceSto ckByPN","S ervicePart ='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!
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_MinServiceStockByP
WHERE [Master Parts Data].AutoMinStockOverride
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","C
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!
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"
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_MinServiceStockByP N.SumOfMin Stock show valid data, and show it as expected (that is, the sums are correct for the P/N)?
Does CG3T_11_MinServiceStockByP N.SumOfMin Stock include the correct P/N in the ServicePart field for the Summed data?
Is AutoMinStockOverride a Boolean field?
Does CG3T_11_MinServiceStockByP
Does CG3T_11_MinServiceStockByP
Is AutoMinStockOverride a Boolean field?
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_MinServiceStockByP N 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!
The CGT3_11_MinServiceStockByP
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_MinServiceStockByP N ON [Master Parts Data].[P/N]=CGT3_11_MinSer viceStockB yPN.Servic ePart
WHERE [Master Parts Data].AutoMinStockOverride =False;
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_MinServiceStockByP
WHERE [Master Parts Data].AutoMinStockOverride
<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.
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.
ASKER
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!
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?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Open in new window