I think your best bet would be to approach this from a coding prespective
dim db as database: set db = currentdb
dim rs as recordset: set rs = db.openrecordset("SELECT ProjectID FROM tblYourTable WHERE ProjectMonth = 14 AND ApprovedStatus = 1")
if rs.absoluteposition = -1 then exit sub
while not rs.eof
db.execute("UPDATE tblYourTable SET ApprovedStatus = 1 WHERE ProjectMonth = 15 AND ProjectID = '" & rs.fields(0) &"'")
This gives you a dataset that is just those projects in the 14th month that were approved.
It then looks at the same table for those projects that have the same project ID and updates the approved status to 1 where the project month is = 15
You definitely have the right idea but it seems to be changing all entries of month15 to a 1 regardless of what APPROVEDSTATUS is for month14. The strange part is that the SELECT statement with the EXISTS statement pulls in the correct group that should be changed.
Originally Posted by Teddy
Oh... is that it? You want to set the approved status to 1 for month 15 where month 14 is already approved?
SET approvedStatus = 1
WHERE MonthNumber = 15 AND EXISTS (SELECT * FROM yourTable t1 WHERE t1.ProjectID = yourTable.ProjectID AND ApprovedStatus = 1)