OOO!
I didn't understood that you want KM for MAX(date) not MAX(km). Try this:
Update RptTjedniKm ;
Set Startkm = Kmtr.Km;
FROM RptTjedniKm;
INNER JOIN (Select kilometri.ID, datum, kilometri.stanjekm AS Km;
From kilometri;
INNER JOIN (SELECT ID, MAX(datum) AS datum;
FROM kilometri;
GROUP BY Id WHERE datum < ldDatod) Klmtr;
ON kilometri.Id = Klmtr.Id AND kilometri.Datum = Klmtr.Datum) Kmtr;
ON RptTjednikm.ID = Kmtr.ID
(not tested)
>Now I get results without errors, but wrong - still I have a max kms, not kms for max date... :) Result is same as before (my query)...
>
>
>>My mistake, change:
>>
Max(kilometri.stanjekm)
>>to
>>
Max(kilometri.stanjekm) AS MaxKm
>>
>>
>>>I get "Column MAXKM is not found"?
>>>
>>>
>>>>MaxKm is a field of derived table, result of this query:
>>>>INNER JOIN (Select kilometri.ID, Max(kilometri.stanjekm);
>>>> From kilometri;
>>>> GROUP BY Id WHERE datum < ldDatod) Kmtr;
>>>>
>>>>
>>>>>Hmm. what is Kmtr.MaxKm??? SQL and I are confused :))
>>>>>
>>>>>>>Hi!
>>>>>>>
>>>>>>>I have table kilometri with 3 fields:
>>>>>>>ID, datum, stanjekm.
>>>>>>>
>>>>>>>I want to update another table with stanjekm for max date which is less then some other date...
>>>>>>>Here is code which work, but update for max stanjekm. This work, but when someone change km meter in his car, I get wrong result, of course....
>>>>>>>How to get stanjekm for max datum, but less then ldDatod?
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.