Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2019, 01:21 AM   #1
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Using Group By in Update Query

Is there way to use Group By in Update Query?
Certain entity could be getting number of dates, I wish to be able to update the latest date.

Jonny is offline   Reply With Quote
Old 10-13-2019, 01:32 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Using Group By in Update Query

it can be.
create a Group by query and linked the query on the table that you want to update.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Jonny (10-13-2019)
Old 10-13-2019, 02:21 AM   #3
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Using Group By in Update Query

Quote:
Originally Posted by arnelgp View Post
it can be.
create a Group by query and linked the query on the table that you want to update.
getting "operation must use an updateable query"

Jonny is offline   Reply With Quote
Old 10-13-2019, 06:06 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,577
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Using Group By in Update Query

If there is an aggregate function involved in the GROUP BY query then that would make it impossible to update. In fact, per Allen Browne (see link below), the mere presence of the GROUP BY clause makes it no updateable. But here is my question: Why not just use ORDER BY instead? If you were not intending to form an aggregate function over the group, then there is no major difference between the two. If the field is "groupable" then it is also sortable and will bring all the records with the same field value together but individually.

http://allenbrowne.com/ser-61.html
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-13-2019, 06:09 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Using Group By in Update Query

Hi. So, rather than a GROUP BY (Totals) query, try using DMax() in your UPDATE query. Hope it helps...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (10-13-2019)
Old 10-13-2019, 01:12 PM   #6
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Using Group By in Update Query

Quote:
Originally Posted by theDBguy View Post
Hi. So, rather than a GROUP BY (Totals) query, try using DMax() in your UPDATE query. Hope it helps...
I like this solution, works smoothly for me.. here is it!
Code:
UPDATE tbl SET myDate1= #12/12/2012#
WHERE myDate2=DMax("myDate2","tbl","ID=111");
Thanks!
Jonny is offline   Reply With Quote
Old 10-13-2019, 02:05 PM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Using Group By in Update Query

Quote:
Originally Posted by Jonny View Post
I like this solution, works smoothly for me.. here is it!
Code:
UPDATE tbl SET myDate1= #12/12/2012#
WHERE myDate2=DMax("myDate2","tbl","ID=111");
Thanks!
Hi. You're welcome! Glad to hear you got it to work. Good luck with your project.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (10-13-2019)
Old 10-13-2019, 04:27 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Using Group By in Update Query

Apparently no one wanted to mention the problem with storing calculated values.

Rather than storing a value from another table, the best solution is to get it when you need it. That way you won't run into data anomalies when the date changes but your update query hasn't run.

Please do some reading on normalization to gain an understanding of the issues.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Jonny (10-13-2019)
Old 10-13-2019, 10:34 PM   #9
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Using Group By in Update Query

Quote:
Originally Posted by Pat Hartman View Post
Apparently no one wanted to mention the problem with storing calculated values.

Rather than storing a value from another table, the best solution is to get it when you need it. That way you won't run into data anomalies when the date changes but your update query hasn't run.

Please do some reading on normalization to gain an understanding of the issues.
Thank you for challenging me, I did it this way and that works too!
Code:
UPDATE tbl1 SET tbl1.myDate1 = #12/12/2012#
WHERE tbl1.myDate2 =(SELECT Max(tbl1.myDate2) AS MaxOfmyDate2
FROM tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID
GROUP BY tbl2.EmpID
HAVING tbl2.EmpID="xxx");
Jonny is offline   Reply With Quote
Old 10-14-2019, 06:56 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Using Group By in Update Query

Quote:
Originally Posted by Jonny View Post
Thank you for challenging me, I did it this way and that works too!
Code:
UPDATE tbl1 SET tbl1.myDate1 = #12/12/2012#
WHERE tbl1.myDate2 =(SELECT Max(tbl1.myDate2) AS MaxOfmyDate2
FROM tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID
GROUP BY tbl2.EmpID
HAVING tbl2.EmpID="xxx");
Hi. Thanks for the update. If you did a speed comparison between the two approaches, which one executes faster and by how much? Just curious...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Option Group Update jmills Forms 2 08-07-2019 11:01 PM
group by query and adding an updatable checkbox fiels to update a table grain_r General 13 10-28-2014 01:26 PM
UPDATE, GROUP BY, MAX question? triplee23 Queries 3 04-22-2012 06:06 AM
Update statement cannot use group by? lok1234 Queries 1 03-16-2009 07:32 PM
Update With Group By Query SkiGeek Queries 4 08-28-2007 03:59 PM




All times are GMT -8. The time now is 06:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World