>>>>>Ok, played around with that a bit and I've decided that I'm not going to try and do it in one SQL, but just try to get what I need with a second one.
>>>>>
>>>>>Initial result from first SQL:
>>>>>
>>>>>
>>>>>DATEFIELD1 DCONTRIB DEFFECTIVE FIELD1 NPREMPAID NITDSURRAM
>>>>>11/02/04 11/02/04 .NULL. 8500.00 8500.00 .NULL.
>>>>>11/02/04 11/02/04 .NULL. 10800.00 10800.00 .NULL.
>>>>>11/02/04 11/02/04 .NULL. 14100.00 14100.00 .NULL.
>>>>>11/02/04 11/02/04 .NULL. 14796.52 14796.52 .NULL.
>>>>>11/02/04 11/02/04 02/11/05 14100.00 14100.00 85164.30
>>>>>11/02/04 11/02/04 02/14/05 14100.00 14100.00 85164.30
>>>>>11/02/04 11/02/04 02/15/05 14100.00 14100.00 85164.30
>>>>>
>>>>>
>>>>>Need a second SQL to get this desired results (this is what I can't get to work right now):
>>>>>
>>>>>
>>>>>DATEFIELD1 DCONTRIB DEFFECTIVE FIELD1 NPREMPAID NITDSURRAM
>>>>>11/02/04 11/02/04 02/11/05 14100.00 14100.00 0.00
>>>>>02/11/05 11/02/04 02/11/05 85164.30 0.00 85164.30
>>>>>
>>>>>
>>>>>Really what I need is DateField1 and Field1, but I thought it would be easier to show them all.
>>>>
>>>>
>>>>
>>>>How you decide which records you want?
>>>>From where DateField1 become 02/11/05?
>>>
>>>Ok, good point. Since the 2nd table is non-normal, I really need a record created from the first occurance where there is a non-zero value in the NITDSURRAM field, or another record if that value changes to a another non-zero value.
>>
>>But in your desired result first value of NITDSURRAM is zero?
>
>Yes. It is zero until it is applied, then it is that value until it changes.
>
>>Also I don't see any record that begins with DATEFIELD1 = 02/11/05
>
>The 2nd record is using the dEffective date, the 1st record is using the dContrib. Essentially, a new record (transaction) is created when the NITDSURRAM becomes non-zero, or changes after that.
Sorry for late reply but I was on my way home :o)
I think that for that kind of logic you must use xBase commands, not SQL.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.