UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Table Based On Left Join...what Am I Missing?, Access 2013    
 
   
PinkishToe
post Jul 31 2020, 10:24 AM
Post#1



Posts: 4
Joined: 8-July 20



Hi all,

I have three tables in my query. And somewhere near 22000 records that must be updated. I currently loop through a recordset in vba, select information from T3 for each row and update the fields however as you can imagine that takes too much time. I would like to update the fields through a query. This should be a very easy task, updating on a left join...

I am getting an error of "Recordset is not updatable".

I have 3 tables. T1, T2, T3. Their structures are as follows (simplified):
T1 (ID, UserID)
T2 (ID, T1ID, Rate) - T1 and T2 have a 1 to 1 relationship set up on T1.ID = T2.[T1ID]

T3 (ID, UserID, Rate) - T3 has multiple userID/Rate combinations.

Here is an example of the query:

Update
(
T1
Inner Join T2
On T1.[ID] = T2.[T1ID]
)
Left Join
(
Select [UserID], Max(Rate) As UsrRate
FROM T3
GROUP BY [UserID]
) T3_Sub
ON T1.[UserID] = T3_Sub.[UserID]

SET T2.[Rate] = T3_Sub.[UsrRate]

Thank you in advance.
This post has been edited by PinkishToe: Jul 31 2020, 10:30 AM
Go to the top of the page
 
theDBguy
post Jul 31 2020, 10:32 AM
Post#2


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Well, LEFT JOINs sometimes render a query as "read-only." However, a GROUP BY will "always" do that. So, that's probably what's causing your problem.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
PinkishToe
post Jul 31 2020, 10:47 AM
Post#3



Posts: 4
Joined: 8-July 20



Is there no way to update records with an aggregate sub function using a join? That seems like quite the downfall.
Go to the top of the page
 
theDBguy
post Jul 31 2020, 11:49 AM
Post#4


UA Moderator
Posts: 78,612
Joined: 19-June 07
From: SunnySandyEggo


Hi. Head over to Allen Browne's website for some recommendations. One approach is to use DLookup() instead.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jul 31 2020, 12:59 PM
Post#5



Posts: 3,848
Joined: 27-February 09



Not sure how that's supposed to work... I always though the syntax for update was

UPDATE <tableName> t1
SET t1.columnName = <expression>
FROM...
[INNER] JOIN...

Go to the top of the page
 
GroverParkGeorge
post Jul 31 2020, 01:45 PM
Post#6


UA Admin
Posts: 37,630
Joined: 20-June 02
From: Newcastle, WA


This is pretty much the way SQL has worked since it was invented.

Think about it. You GROUP several records together to get an average or sum or count. How could you then decide which related records to update, and which ones not to update? All of them? Only one? Which one--remember we're talking about aggregates?

There are alternatives, as have been proposed.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Jul 31 2020, 02:20 PM
Post#7


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


Adding to previous comments, for it to work it is likely you will need to specify unique records once you have fixed your update query.
In SQL view that will be UPDATE DISTINCTROW ....

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
projecttoday
post Jul 31 2020, 05:15 PM
Post#8


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


Of course logically you can update a table from a query based on a common field(s). In Access you can update one table from another table. If I am not mistaken it does not support updating a table from a saved query. So you would have to put the query results into a temporary table.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Jul 31 2020, 05:32 PM
Post#9



Posts: 1,612
Joined: 25-January 16
From: The Great Land


Definitely can use a saved query object as source for update.

Saving calculated data, especially aggregate data, is usually a bad idea. If you can calculate the aggregate data for UPDATE then it can be calculated when needed.

Otherwise, use domain aggregate functions, or open a recordset in VBA and loop through records, or save aggregate records to a temp table and use table as source.

This post has been edited by June7: Jul 31 2020, 05:36 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
isladogs
post Jul 31 2020, 05:33 PM
Post#10


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


Sorry Robert but you are wrong.
Access will allow you to update a table from a saved query providing that query doesn't cause the update query to be effectively read only.
A simple test will confirm that it can be done.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
projecttoday
post Jul 31 2020, 05:44 PM
Post#11


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


I did indicate that I wasn't sure on that point.

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Jul 31 2020, 08:16 PM
Post#12


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi guys: I have been following this thread, with an idea stuck in the back of my head that the reason this is a problem in this case is because of the table design/relationships. To get the max of the rates from T3 is easy. BUT, to get the right key from T2 so an update can occur, one must link T3 max rate (a totals query) through T1 to T2 in a query. This means that T2 is being used in the query stack to find the specific max rate to update T2. Thus, it seems to me, this will lock out T2 from being updated. I hope I understood this correctly - and explained this correctly. It twisted my brain a bit to get to here.

My workaround was to use a maketable query to save the max rates and associated keys, then use the new table as the data source to perform the update query on T2.

I guess this is more easily seen in a demo, which I have attached.

I would be very happy to be wrong on this, and that this can be done without the maketable approach. But the maketable approach seems to work fine until a better way is demonstrated.

Take a look. Use the first button on the form to look at the current values in T2. Then, with the second button, run the update and see the results in T2. Take a look at the code and the queries in the db to see how this is done. And, of course, back to the tables to see if this is what the OP described. And, as I said, I would be happy if there was a direct way to do this.

However, after all this, I wonder why a query cannot simply be used to get the data you want, rather than updating a table...

HTH
Joe
Attached File(s)
Attached File  UpdateTable_MakeTableApproach.zip ( 25.77K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 04:21 AM