@seth I read so many similar article. but It's not suit to my situation.
their field is number have order. but In my situation, those number mixed by ID (1 and 2) and date too.
well, the good news, I found the way to do this
with your suggestion from other post.
- SELECT Table1.ID, Table1.Date, last(tableLookup.km) AS km
-
FROM Table1 INNER JOIN tableLookup ON Table1.id = Tablelookup.id and table1.date >= tablelookup.datestart
-
GROUP BY Table1.ID, Table1.Date;
-
very clear and clever relationship combine with last function.
but I findout 1 bug with above code:
if i change datestart of tablelookup, the result will not correct anymore, how to solve this problem ?
edit: the problem is last function depend on index of ID fields, so if i switch 2 date like 1 switch to 3 (vise versa) of ID = 2 tablelookup then result won't be correct ? if i remove index of id, it'll be completely wrong result.
edit2:
ok, finally i have to use DAODB.recordset in a function
first, open that table with condition and order by ID and Datestart.
then use recordset.movelast
after that get that last value from function...
edit3:
recordset too slow and not giving correct answer, need too much tweak.
I'm trying subquery
Finnally: subquery with sometweak work well with stacked query in side total query... I didn't know this problem lead me to this far ==