I have 2 table.
table 1 with [ID] and [Date]
ID Date
1 -- 1
1 -- 2
1 -- 3
2 -- 1
2 -- 2
2 -- 3
Table 2 with [ID], [Datemin], [DateMax], and [KM]
ID Datemin DateMax KM
1 ---- 1 ----- 2 ------ 100
2 ---- 1 ----- 2 ------ 100
2 ---- 3 ----- 4 ------ 200
when I create query like this
Expand|Select|Wrap|Line Numbers
- SELECT Table1.ID, Table1.Date, IIf([table1].[date] Between [table2].[from] And [table2].[to],[table2].[KM],0) AS KM
- FROM Table1 LEFT JOIN table2 ON Table1.IDItem = table2.IDItem;
but I got duplicate from it. Instead 6 rows like table 2, query gave me 9 rows with duplicate 3 rows from ID 2
how to avoid/eliminated those duplicate ?