Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-21-2019, 09:48 AM   #1
Freshman
Newly Registered User
 
Freshman's Avatar
 
Join Date: May 2010
Location: Jerusalem, Israel
Posts: 370
Thanks: 23
Thanked 7 Times in 7 Posts
Freshman is on a distinguished road
Update Flag on Max Date

Hi all,

I've seen the answer to my question on a few places but just can't seem to implement it on mine. Maybe I'm missing something when changing their fields to mine, so I'll give my table and field names if someone can help please

Table: Career
Fields:
CareerID AutoNum and PK
ID Num (relationship to main table not relevant now)
SinceDate Date in question
MrkC Flag

I need to set the MrkC flag to -1 for the Max 'SinceDate' of each 'ID' row

I keep getting a not-updatable query error

Thanks a lot

__________________
Windows 7
MS Access 2007

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Freshman is offline   Reply With Quote
Old 09-21-2019, 10:08 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Update Flag on Max Date

Please post the SQL of your not-updatable query.
It would be helpful to readers if you would provide a short description of what you're trying to do. And a few samples of your desired output would help with context.
__________________

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.
jdraw is offline   Reply With Quote
Old 09-21-2019, 10:27 AM   #3
Freshman
Newly Registered User
 
Freshman's Avatar
 
Join Date: May 2010
Location: Jerusalem, Israel
Posts: 370
Thanks: 23
Thanked 7 Times in 7 Posts
Freshman is on a distinguished road
Re: Update Flag on Max Date

Hi there, sorry I thought I was clear enough.
I have a table with multiple 'SinceDate' for each 'ID' row
So I need to flag the Max 'SinceDate' for each 'ID'

I attached a database file where I do that using no less than 3 queries and one temp table - madness

PS: Hold down shift on open
Attached Files
File Type: accdb test.accdb (376.0 KB, 7 views)

__________________
Windows 7
MS Access 2007

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Freshman is offline   Reply With Quote
Old 09-21-2019, 10:31 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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: Update Flag on Max Date

Code:
update Career inner join 
     (select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T 
     on career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1] 
set career.MrkC = -1;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-21-2019, 10:47 AM   #5
Freshman
Newly Registered User
 
Freshman's Avatar
 
Join Date: May 2010
Location: Jerusalem, Israel
Posts: 370
Thanks: 23
Thanked 7 Times in 7 Posts
Freshman is on a distinguished road
Re: Update Flag on Max Date

Quote:
Originally Posted by arnelgp View Post
Code:
update Career inner join 
     (select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T 
     on career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1] 
set career.MrkC = -1;
Thanks for the speedy reply. Field is [ID] not [ID Num] sorry seems like I put the Data type to close to the field name.

Even when changing that I get the following error:

The SELECT steement included a reserved word or an argument name that is misspelled or misisng or the puntuation is incorrect

Here is the SQL I used:

update Career inner join
(select [ID], max(SinceDate) as Expr1 group by [ID] from Career) AS T
on Career.[ID] = T.[ID] and Career.[SinceDate]=T.[Expr1]
set Career.MrkC = -1
__________________
Windows 7
MS Access 2007

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Freshman is offline   Reply With Quote
Old 09-21-2019, 10:54 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,383
Thanks: 434
Thanked 804 Times in 779 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Update Flag on Max Date

Well it is hardly sensible using [ID] if the field is called [ID Num]?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is offline   Reply With Quote
Old 09-21-2019, 10:59 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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: Update Flag on Max Date

create a query (query1)
Code:
SELECT career.id, Max(career.sincedate) AS Expr1
FROM career
GROUP BY career.id;
create the "final" update query adding query1 on the sql:
Code:
UPDATE Career SET Career.MrkC = -1
   WHERE career.careerid in  
   (select careerid from career t1 
   inner join query1 on t1.id = query1.id and t1.sincedate=query1.Expr1);

__________________
"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:
Freshman (09-21-2019)
Old 09-21-2019, 11:03 AM   #8
Freshman
Newly Registered User
 
Freshman's Avatar
 
Join Date: May 2010
Location: Jerusalem, Israel
Posts: 370
Thanks: 23
Thanked 7 Times in 7 Posts
Freshman is on a distinguished road
Re: Update Flag on Max Date

Quote:
Originally Posted by arnelgp View Post
create a query (query1)
Code:
SELECT career.id, Max(career.sincedate) AS Expr1
FROM career
GROUP BY career.id;
create the "final" update query adding query1 on the sql:
Code:
UPDATE Career SET Career.MrkC = -1
   WHERE career.careerid in  
   (select careerid from career t1 
   inner join query1 on t1.id = query1.id and t1.sincedate=query1.Expr1);
Thanks for the above - is there no way to combine it in a single update query?
Else it will have to do and in anyway much better than what I had
__________________
Windows 7
MS Access 2007

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Freshman is offline   Reply With Quote
Old 09-21-2019, 11:07 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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: Update Flag on Max Date

I already tried on my first post, but no..
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-21-2019, 11:08 AM   #10
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,383
Thanks: 434
Thanked 804 Times in 779 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Update Flag on Max Date

Quote:
Originally Posted by Gasman View Post
Well it is hardly sensible using [ID] if the field is called [ID Num]?
Well you changed your last post. but I owe you an apology. I misread the post as field being called [ID Num] as arnel had already used ID in his code?, so why mention it?

Getting late for me as well.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

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.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Freshman (09-21-2019)
Old 09-21-2019, 11:19 AM   #11
Freshman
Newly Registered User
 
Freshman's Avatar
 
Join Date: May 2010
Location: Jerusalem, Israel
Posts: 370
Thanks: 23
Thanked 7 Times in 7 Posts
Freshman is on a distinguished road
Re: Update Flag on Max Date

Quote:
Originally Posted by Gasman View Post
I owe you an apology

All good - Coding will do that to you I'm off to bed now

__________________
Windows 7
MS Access 2007

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Freshman is offline   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
Date query with a flag grades Queries 3 05-13-2012 09:21 PM
Update query for Boolean flag? fredalina Queries 8 07-16-2008 05:00 AM
Multiselect Listbox selection and update flag in table Suresh62 Forms 0 12-19-2007 05:27 AM
Flag changes with date Sohaila Taravati Reports 3 10-02-2002 08:56 AM




All times are GMT -8. The time now is 01:17 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