Double hit in Query

[Steffe1611]

Hi.

Maybe a simple question, but is confusing me a lot.

I compare two lists and the query returns the below list

The query returns the correct search in the Left part of the list but it makes a double line en the other side (marked with RED)

SQL code: SELECT [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)].[Part_sum (With colour critiria)].[Lego part], [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)].[Part_sum (With colour critiria)].farve, [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)].Beskrivelse
FROM [Set 7897-1] LEFT JOIN [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)] ON [Set 7897-1].[Lego part] = [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)].[Part_sum (With colour critiria)].[Lego part];

How can I return/remove the double (3 times) results in the right row.

Thanks.

Steffen
Set 7897-1.Lego partSet 7897-1.farveAntalPart_sum (With colour critiria).Lego partPart_sum (With colour critiria).farveBeskrivelse3005Red2


3005White10


3004Black143004RedBrick 1 x 23004Red103004RedBrick 1 x 23004White183004RedBrick 1 x 22877DkStone16


2877Red8

 

[jdraw]

It sounds like a database structure problem, but we know nothing about what you are trying to do. Show us your tables--- fldname and data types. And give us a plain English overview of WHAT you are doing.

 

[CJ_London]

try distinct

SELECT DISTINCT [Set 7897-1]....

Also check your post before you send it - the bit in red is unreadable to someone who doesn't know what they are looking at

 

[Steffe1611]

Hi.

I have tried the distinct part but I can´t get to work
Maybe I am on a wrong track but I will try to explain form the start.

I have:
1. Query (attached as file) - 622 Records
1. Tables (attached as file) - 111 Records

With the following criteria's:
1. Part
2. Colour
3. pcs.

Wanted results:
1. Equal match (same part, Colour and pcs (pcs difference above and equal 0) - 16 results in this example
2. Equal match (same part, colour but missing pcs. (pcs difference below 0) - 5 results in this example
3. Missing parts (difference in org. list - 1 and 2 query) - (111-16-5) results in this example


I assume I need 3 Queries, but how will they look.?
Can this be done more simple.?


I hope someone can help me as I am stuck.

Thanks.

Steffen

 

[Steffe1611]

Now with attachments

 

[CJ_London]

please post the files as .csv or excel - although these demonstrate your 'before and after' I cannot apply your query to it.

Also, you have two tables in your query [Set 7897-1] and [Test_Kopi af Gap_Ens dele med OK Antal (incl farve)] .

It would also be helpful to name your files the same as used in your query - I don't know which the above T_Set refers to

 

[Steffe1611]

Hi

I have attached the two files (in several formats)
I don't think it makes sense looking at my old query as you properly have a smarter way to compare this.

I have:
1. Query (attached as file) - 622 Records
1. Tables (attached as file) - 111 Records

With the following criteria's:
1. Part
2. Colour
3. pcs.

Wanted results:
1. Equal match (same part, Colour and pcs (pcs difference above and equal 0) - 16 results in this example
2. Equal match (same part, colour but missing pcs. (pcs difference below 0) - 5 results in this example
3. Missing parts (difference in org. list - 1 and 2 query) - (111-16-5) results in this example

Thanks.

Steffen

 

[CJ_London]

I'll see what I can do - The only files of any use are the excel files, but there is a blank line in one of them - please confirm this is not required for anything else

 

[Steffe1611]

Hi.

The blank line just need to be deleted.

Thanks for spending your time.

Steffen