Hi Sergey,

I was hoping to get something that I could edit in the view designer.

I'm trying to write a small app that the owner of the company could maintain himself. He wants to keep it as simple as possible. He likes the view designer, and doesn't really want to learn to write select statements himself (and I'm not convinced that he could get them right).
He has a dozen or so files like the t1 file, where he needs the values that were effective on a certain date in the past.

I've pretty much reached the conclusion that he will just have to learn to maintain some Prgs that contain this query statement in them, or he'll have to call me back to maintain them. My bigger problem, is that he won't be convinced that it can't be done, that I'm only trying to get more money out of him. But that's the kind of trouble I get into when I take on a client who knows a little (but not enough) about FoxPro. From now on, I take on only "dumb" clients.


>What you're tring to acomplish? I'm not sure what is your definiton of 'steps' for a query and why it's so importent that there was only one step in a query. Anyway, following query uses correlated subquery.

SELECT * FROM t1 ;
>	WHERE EffectiveDate IN ( ;
>		SELECT max(EffectiveDate) ;
>  from t1 t1_2 ;
>  where EffectiveDate <= ?dParam ;
>  	AND t1_2.Empid = t1.empid )
>

>
>>Hi Sergey,
>>This is still 2 steps, just combined onto 1 line of code.
>>I was really hoping for something that would only "use t1 shared" once during the process.
>>This is probably the best that can be done, but I'm still hoping.
>>
>>
>>>This query should work

SELECT * FROM t1 ;
>>>	WHERE STR(EmpId) + Dtos(EffectiveDate) IN ( ;
>>>		Select Str(EmpID) + Dtos(max(EffectiveDate)) ;
>>>  from t1 ;
>>>  where EffectiveDate <= ?dParam ;
>>>  group by EmpID )
>>>

>>>