I have a table that maintains the history of contract "grades". I need to update the archive indicator with a "y" for grades that "failed" the previous run date but "pass" on the current run date. I am working with only one table that is self joined. The query runs but it will not update the archive indicator. Please help.

There can be "thousands" of reasons why you don't get what you want.
Post your query.
Is your archive indicator a Yes/No field?

RV

No, it is a text field. In my query, I am telling it to update the archive_indicator field to "Y" if in the 1st table the certgrade is "Fail" and in the second table the certgrade is "Pass". I have one table self joined. I have the fields contract number and run date inner joined.

The table maintains a history of contract grades by run date. I need to compare the previous run date with the current run date to see if the certgrade changed. I only want to archive the "fail" certgrades for the previous month if the "pass" the current month.

Let me know if you need more info.

>Let me know if you need more info<

It would be helpfull if you posted your SQL statement.

RV

RV
This is my SQL statement:

UPDATE DISTINCTROW tblTenantCert_History1 AS tblTenantCert_History1_1 INNER JOIN tblTenantCert_History1 ON tblTenantCert_History1_1.Contract_Number = tblTenantCert_History1.Contract_Number SET tblTenantCert_History1.Archive_Indicator = "Y"
WHERE (((Mid([tblTenantCert_History1]![Contract_Number],1,2)) In ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL' ,'GA','GQ','HI','IA','ID','IL','IN','KS','KY','LA' ,'MA','MD','ME','MI','MN','MO','MS','MT')) AND ((tblTenantCert_History1.CertGrade)="Fail") AND ((tblTenantCert_History1_1.CertGrade)="Pass"));

Savannah,

you're trying to update a table using an autojoin on a table.
I've read your SQL statement, it won't work (never works this way):

((tblTenantCert_History1.CertGrade)="Fail") AND ((tblTenantCert_History1_1.CertGrade)="Pass"))
This condition will never be met.
You're comparing a row with itself, the CertGrade is either "Pass" or "Fail".

You'll have to create a table HistoryGrades in which you can "track and trace" the status of a Grade.
This new table will have a one to many relationship with your current table (many on the side of your new table), using Contract_Number as a foreign key in the new table.

RV