Update Flag on Max Date

[Freshman]

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

 

[jdraw]

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.

 

[Freshman]

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

 

[arnelgp]

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;

 

[Freshman]

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

 

[Gasman]

Well it is hardly sensible using [ID] if the field is called [ID Num]?

 

[arnelgp]

create a query (query1)

SELECT career.id, Max(career.sincedate) AS Expr1
FROM career
GROUP BY career.id;

create the "final" update query adding query1 on the sql:

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);

 

[Freshman]

create a query (query1)

SELECT career.id, Max(career.sincedate) AS Expr1
FROM career
GROUP BY career.id;

create the "final" update query adding query1 on the sql:

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

 

[arnelgp]

I already tried on my first post, but no..

 

[Gasman]

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.

 

[Freshman]

I owe you an apology

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