Hi All,
Please i have this data
| Key | PrevKey | Creation Date |
|---|
| 1-A | 1 | 1/1/2011 |
| 1-A-A | 1-A | 2/1/2011 |
| 1-A-A-A | 1-A-A | 3/1/2011 |
| 1-A-A-A-A | 1-A-A-A | 4/1/2011 |
And i need to add a new field which is NewDate
so the final table have to be
| Key | PrevKey | Creation Date | NewDate |
|---|
| 1-A | 1 | 1/1/2011 | 1/1/2011 |
| 1-A-A | 1-A | 2/1/2011 | 1/1/2011 |
| 1-A-A-A | 1-A-A | 3/1/2011 | 1/1/2011 |
| 1-A-A-A-A | 1-A-A-A | 4/1/2011 | 1/1/2011 |
i need the macro which calculate the NewDate according to the following Criteria:
for (i=0 ; i<count(PrevKey) ; i++)
for (j=0 ; j<count(Key) ; j++)
{
if ( PrevKey = Key)
NewDate=Min(NewDate)
else
NewDate=CreationDate
}
i.e i need for each PrevKey to be compared with the whole Key field
and when the PrevKey being compared is equal to the Key in any other row to take the minimum date of the NewDate field and to be as the NewDate
or if this is impossible i need another solution
Thanks in Advance.
