Assume you table [MyTable] has a structure something like below:
field01 - key
field02 - type
refdate01 - date
refdate02 - date
refdate03 - date
refdate04 - date
refdate05 - date
refdate06 - date
refdate07 - date
newfield - date
field(nn) - type
Go to Query | New Query |SQL View
update MyTable as A, MyTable as B
set a.newfield = b.refdate01
where a.field01 = b.field01;
[Note: Make sure you join all fields
in your primary key if you have a
complexe (more than one field) primary key]
Run the query 'press the RED !' - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate02
where a.field01 = b.field01
and a.newfield < b.refdate02;
Run the query - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate03
where a.field01 = b.field01
and a.newfield < b.refdate03;
Run the query - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate04
where a.field01 = b.field01
and a.newfield < b.refdate04;
Run the query - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate05
where a.field01 = b.field01
and a.newfield < b.refdate05;
Run the query - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate06
where a.field01 = b.field01
and a.newfield < b.refdate06;
Run the query - change the query to:
update MyTable as A, MyTable as B
set a.newfield = b.refdate07
where a.field01 = b.field01
and a.newfield < b.refdate07;
Run the query - your're done.
--------
Long term, I'd name each query above [Save As] and create a macro with an action of 'OpenQuery' and specify each query's name in the 'Query Name' attribute at the bottom of the screen, on each of the seven lines in the macro. Then simply run the macro when you want your table updated.
