473,320 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

215 128KB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table1.Date, IIf([table1].[date] Between [table2].[from] And [table2].[to],[table2].[KM],0) AS KM
  2. FROM Table1 LEFT JOIN table2 ON Table1.IDItem = table2.IDItem;
  3.  
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 ?
Oct 7 '15 #1
8 1082
jimatqsi
1,271 Expert 1GB
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
Oct 7 '15 #2
hvsummer
215 128KB
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 ?
Oct 8 '15 #3
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
Oct 8 '15 #4
Rabbit
12,516 Expert Mod 8TB
You said you wanted "have date between datemin and datemax" but nowhere in your SQL do you do that.
Oct 8 '15 #5
hvsummer
215 128KB
@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.
Oct 9 '15 #6
Rabbit
12,516 Expert Mod 8TB
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.
Oct 9 '15 #7
hvsummer
215 128KB
@Rabbit: how ? I can't deal with that many to many relationship ==
Oct 9 '15 #8
Rabbit
12,516 Expert Mod 8TB
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.
Oct 9 '15 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: audj | last post by:
Hello, I am trying to use a subquery to avoid duplicate entries when someone submits a form to subscribe to a mailing list. So I want to check if the email exists before adding the record. ...
5
by: mercea | last post by:
hi all, How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks...
2
by: Shawn Yates | last post by:
I am creating a Zip Code search tool. I have a Table with every zip code and its corresponding city and state. I have also created an input table that allows the user to input cities and states....
4
by: lucky13 | last post by:
Dear All, can u help in how to check the duplicate value in the listview control before adding new in the list. Regards Lucky13
1
by: vishwa Ram | last post by:
Hi ScriptFamily, I need to remove a duplicate value in array. Any specific method available in perl. pls suggest Regards Raam
3
by: spl | last post by:
What is the fastest way to find a duplicate value in an array of 10 elements. I have to find just any one first occurrence of the duplication. Lets say I have ary={10, 20, 40, 90, 30, 60, 35, 40,...
1
by: krishanusasmal | last post by:
I am learning in PHP and HTMl. AND XAMPP. when i inserting any duplicate value. I want to display message with duplicate message or the data already exist or something like that. Plz. help me.
1
by: inder | last post by:
I have two fields Phone1 and Phone2 in my form. I want Phone1 after update to check its duplicate value in both phone1 and phone2. Similarly Phone2 to check phone2's duplicate value in both phone1...
5
by: hvsummer | last post by:
ok, After I used MVFs (Multivalue Fields) to query thing, I got alittle problem with it, It created duplicate value in query, can anyone spot out the problem and give some solution idea ? the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.