Find different values in same field for same record ID

[tavie]

HI...I have a history table that contains a field called type for an active record. The active record also has a field called flag that will always be a value of 1 and it's previous now inactive record will always be 0. What I am having difficulty doing is determining if the type value is different between the active record and the inactive record if they are different then I need to write the new type value to another table. I am not sure what type of query to write, so far nothing seems to work. Please help, I'm drowning.......Below is a rough example of the table....

ACCT TYPE FLAG DATE
123 A 1 05/07/03 Active record
123 B 0 04/11/02 Inactive Record
123 c 0 04/10/01 Inactive Record
123 c 0 03/10/00 Inactive Record

FYI...I did not design the database nor can I modify the Schema.......

 

[swampBoogie]

select ht1.acct,ht1.type from historyTable ht1 
 where flag = 1
   and type not in (select type 
     from historyTable
   where acct = ht1.acct
     and flag = 0)

 

[tavie]

Swampboogie That was exactly what I needed Thank you very much, My head just stopped throbbing when this code executed.....thank you again for your help.....