how to avoid duplicate value from this situation, what is the right SQL ?

[hvsummer]

ok, to be simple, I'll ask short question with short detail.

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

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;
 

to determine whether "that ID have date between datemin and datemax" or not, if True, give that ID field value from table2.KM

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 ?

[jimatqsi]

hvsummer, your questions is not very clear because you have been inconsistent in your descriptions. Your SQL refers to fields 'IDItem' when no such field appears in the two table layouts. I presume IDITEM and ID are one and the same. Also, you describe tables 1 and 2, but the sQL refers to tables 4 and 5. And neither of the described tables has a field for From or To, which also appear in your SQL as part of table 5.

I get it, table 1 is table 4, table 2 is table 5, etc.... But don't make us work so hard to understand your question.

All that being said, you need to add grouping to your query. If you right-click in the lower half of the query editor, you'll see a choice for Grouping. In grouping mode, you can specify which columns to group, sum, average and so on. All you need to do is group by both columns being selected and you will get only one row for each pair of values.

You can accomplish a similar thing by right-clicking on the top half of the query editor and choosing Unique Values.

I hope that helps.

Jim

[hvsummer]

sorry jimatqsi, I have edited those code.
and unique value does not help, unique row value too
and grouping can't solve this problem

can anyone suggest on SQL code ? I know that problem is inside the code structure. I think this is many to many relationship duplicate problem.. how to force hide or avoid those duplicate ? or any SQL code can deal with it ?

[lillyehrhart]

in access where you join the tables, you can click on the properties and then from there you can limit your results. you can do all from one table and only match what is in that table, it will help with the duplicates

[Rabbit]

You said you wanted "have date between datemin and datemax" but nowhere in your SQL do you do that.

[hvsummer]

@Rabbit : it's "[table1].[date] Between [table2].[from] And [table2].[to]" that is inside iif. Still thankyou.
@Everyone Else:
I found the way to solve my problem.
should be group with sum(iif(condition, value, 0)) in another query.

then match those value back into first select-query by ID.
thank everyone.

[Rabbit]

You shouldn't do it in the iif, you should do it in the join so that you don't have to deal with duplicates in the first place.

[hvsummer]

@Rabbit: how ? I can't deal with that many to many relationship ==

[Rabbit]

You just take the same thing that's in the first parameter of your iif function call and put it in the join. You'll have to do it in SQL view. You can't use the designer for it.