Hi Boris

Thanks

Sorry, I don't understand. What table am I joining this with? I only want to first "de-dupe" the RteFam table before including it in my later SQL (as you've seen before). No other table comes into it.

Terry

>>>>OK, still on the last-minute panic:
>>>>
>>>>I have a table where each rec has a start_date and end_date. These fields indicate the currency of the records.
>>>>If either is blank then there's no date bound,
>>>>
>>>>e.g blank start_date but there is an end_date means: "This rec is current from the beginning of time until the end date"
>>>>e.g blank end_date but there is an start_date means: "This rec is current from the the given start date until the end of time"
>>>>e.g. both blank means: There are no date bounds on this rec.
>>>>
>>>>I need to eliminate recs with dupe key, getting only the most recent vals, and assume a non-blank start_date is more recent than a blank one. Can't get my head round that today cos of all the other pressures. Pleas ehelp.
>>>>
>>>>'ppreciate it
>>>>
>>>>Terry
>>>
>>>For whole table or for some groups?
>>
>>I just want to filter the whole table according to my start-prev-period date and end-current-period date, so I don't have more than one record for an operator-route key combination.
>>
>>>Also can you have non blank Start date but blank end date?
>>
>>Yes, and vice versa, all as laid out above. But a valid start_date is more recent than a blank one. I'm only interested in the stat dates here:-)
>
>Try first this (I am not sure if this will works on VFP7)
>

>SELECT * FROM MyTable;
>INNER JOIN (SELECT Operator, Route, MAX(Start_Date) AS Start_Date;
>                   FROM MyTable;
>                   GROUP BY Operator, Route) Tbl1;
>      ON MyTable.Operator = Tbl1.Operator AND MyTable.Route = Tbl1.Route AND MyTable.Start_Date = Tbl1.Start_Date;
>INTO CURSOR SupposedNonDups
>

>
>If this not works, try:
>

>SELECT * FROM MyTable;
>WHERE STR(Operator)+STR(Route)+DTOS(Start_Date) =;
>      (SELECT STR(Operator)+STR(Route)+DTOS(MAX(Start_Date)) FROM MyTable Tbl1 WHERE Tbl1.Operator = MyTable.OIperator AND;
>                                                                                     Tbl1.Route = MyTableRoute);
>INTO CURSOR SupposedNonDups
>

>
>If this didn't works either:
>
>

>SELECT Operator, Route, MAX(Start_Date) AS Start_Date;
>                   FROM MyTable;
>                   GROUP BY Operator, Route;
>INTO CURSOR Tbl1
>
>SELECT * FROM MyTable;
>INNER JOIN Tbl1 ON MyTable.Operator   = Tbl1.Operator   AND;
>                   MyTable.Route      = Tbl1.Route      AND;
>                   MyTable.Start_Date = Tbl1.Start_Date;
>INTO CURSOR SupposedNonDups
>