Update a flag for two month ago (1 Viewer)

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
Hello
I have a table with two fields ( date and flag)
I need to find the max Month no. then update flag of the records with 2 months older than max month .
ex ; 08/01/2021 then max month is 8
update flag for records with month less than 6
could you give me a hint how to do ? what should be written in queries and its criteria ???
Thank you in advance
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
14,044
Look at the DateAdd() function and the DMax() function.
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
UPDATE queries are a sign of a poor structure or not understanding how databases work. So, I question the ultimate aim of this.

Why not just build a query and have that query determine the flag? Then when you need to reference the flag you reference the query? What will this flag be used for?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Feb 19, 2002
Messages
42,970
We don't know what the flag means so we are not sure what damage having stored data that can be calculated will cause. But in general, if you can calculate the "status" or whatever this flag is with a query, that is a better method than storing a flag, especially when that flag is date defendant. Tomorrow is a different day. Are you planning on running the update query daily?
 

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
We don't know what the flag means so we are not sure what damage having stored data that can be calculated will cause. But in general, if you can calculate the "status" or whatever this flag is with a query, that is a better method than storing a flag, especially when that flag is date defendant. Tomorrow is a different day. Are you planning on running the update query daily?
no just for a table with 1500 records .I added flag as Yes/NO ,when the max month identified , records with 2 month diffidence updated to yes.
 

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
I guess you didn't hear what we said.
What you are saying is not what i am looking .
I want to turn on each record flag with an update query then delete them .
in the picture below , the max month is 8 . i want to delete records for 05,03,... by marking their flag.
this table changes every moth .So i need max month no. and 2 month later data to be kept in table.

1634328818192.png
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
I guess you didn't hear what we said.
What you are saying is not what i am looking .

And what you are looking for isn't the way to accomplish this....which is...what we both said.

You don't need the flag, you don't need the UPDATE query, you don't need to DELETE data. You simply need a query based on this table that just excludes the records you don't want. Then instead of using the table, you use that query I just described.
 

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
And what you are looking for isn't the way to accomplish this....which is...what we both said.

You don't need the flag, you don't need the UPDATE query, you don't need to DELETE data. You simply need a query based on this table that just excludes the records you don't want. Then instead of using the table, you use that query I just described.
you are right , how to exclude the records ? is my issue with this table .
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
Today is October 15, 2021. What records do you want to exclude?

A. Records prior to August 15, 2021

B. Records prior to August 1, 2021

C. Other
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Jan 23, 2006
Messages
15,364
In essence you want a query to
SELECT records from yourTable
WHERE yourDateField meets yourCriteria


As others have advised--you should research queries.
You are going down the wrong path by adjusting your table.
 

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
Today is October 15, 2021. What records do you want to exclude?

A. Records prior to August 15, 2021

B. Records prior to August 1, 2021

C. Other
Month no. is matter not the date.. Filtering is based on month number .all date were updated in process at the beginning the month :
08/1/2021 or 09/01/2021 or 10/01/2021
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Feb 19, 2002
Messages
42,970
all date were updated in process at the beginning the month :
Another unnecessary update query. So, are you going to run this query every day? every month? If you really want the date to be always the first, than make that happen at data entry time. Don't do it after the fact.

Is this a one time delete? If so, it makes little difference how you do it. If it is a purge that you want to run monthly, then it matters but you can still delete the old data using a delete query that selects just the old records. You do not need to update first and then delete. If a query can select the rows you want to update, it can select those same rows for delete.

Most applications would not do this type of purge ever but those that do, generally keep at least a few year's worth of data and even then they may move the purged records to an archive table so they can be searched if a need arises.
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
You gave me an explanation but no answer. Please answer my question.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Feb 19, 2002
Messages
42,970
So are you now able to make a query that selects what you want to delete assuming you still want to delete the records which we've all recommended against? If you leave decided to leave them in the table, then your query needs to select only the last two months if that is only what you want to see.
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
Here is the select query that will give you the results you want.

Code:
SELECT * FROM YourTableNameHere
WHERE YourDateTable<=DateAdd("m",-2,DateAdd("d",-1*(Day(Date())-1),Date()))

Replace YourTableNameHere with your table's name.
 

mansied

Member
Local time
Today, 11:29
Joined
Oct 15, 2020
Messages
99
Here is the select query that will give you the results you want.

Code:
SELECT * FROM YourTableNameHere
WHERE YourDateTable<=DateAdd("m",-2,DateAdd("d",-1*(Day(Date())-1),Date()))

Replace YourTableNameHere with your table's name.
the function doesn't work properly ,but,Thank you so much , the function DATEADD what i was looking for .I had no clue how to identify records.
I added it to an update query and marked flag to YES ,then I deleted them .
 

plog

Banishment Pending
Local time
Today, 10:29
Joined
May 11, 2011
Messages
11,611
I added it to an update query and marked flag to YES ,then I deleted them .

Well, I'm glad you listened to 5% of what we said.
 

Minty

AWF VIP
Local time
Today, 15:29
Joined
Jul 26, 2013
Messages
10,354
So you changed some data then immediately deleted those records that you have just updated.
Does it not occur to you that at least one of those actions is completely unnecessary?

Just in case you had not noticed, a lot of people were giving you advice on what you should be doing, and you have ignored pretty much everyone.
Think about why you were given that advice as I suspect you won't get a lot more...
 

Users who are viewing this thread

Top Bottom