Compaire two lists

[Steffe1611]

Compaire two lists (on 3 criteria's)

Hi.

I have run into some problems by comparing two lists.
My project is to compare two lists and on the below listed criteria's and with the wanted results.

Criteria's:
1. Part
2. Colour
3. pcs.

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

But already in the first part I run into problems.
I do a query as below. (picture attached)
SQL code:
SELECT Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Parts_All]![Antal]-[Set 7897-1]![Antal] AS Part_Diff
FROM Parts_All RIGHT JOIN [Set 7897-1] ON Parts_All.[Lego part] = [Set 7897-1].[Lego part]
GROUP BY Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal
HAVING (((Parts_All.[Lego part]) Like [All_parts]![Lego part]) AND ((Parts_All.farve) Like [Set 7897-1]![farve]) AND (([Parts_All]![Antal]-[Set 7897-1]![Antal])>=0));

This query is returning 2 of the same lines with part and colour but with different pcs. Why.?
Can this be done more simple.?
Do you have suggestions for the next two steps..?

I hope someone can help me as I am stuck.

Thanks.

Steffen

 

[Drunkenneo]

Could you send the tables by removieng confidential data and replacing with dummy details that will b a help?

 

[Steffe1611]

Hi.

I have attached the two lists.(in several formats, hope one of them fits you)

I expect the query to return.
1. Equal match and pcs = 16
2. Equal match = 5
3. No match = (111-16-5)

Once again thank you for your help.

Steffen

 

[Mihail]

Can you explain a bit better ?
So you have 2 tables: T_Set_1 and Q_Part_S
Use the fields names from THIS tables in order to explain what you want:

Exemple:
T_Set_1 <-> Q_Part_S
Q1:
LegoPart = LegoPart
Farve = Farve
Antal = ????
Answer : 16

Q2:
.....
Answer 5

Q3
....
Answer 111-16-5 = .....

 

[Drunkenneo]

Steffen, Here is the Solution, you have joined on Parts for the query where u need unique colors, Which is not perfect as there are many to many relationship occurs, so suggest you to join on Parts and Color as well for the same.

By this you 2 queries will be achieved:
1) Condition >=0
2) Condition < 0
3) is the right join query 1 and 2 from join query of two tables

 

[Steffe1611]

Hi Mihail

I will try to explain in your format

T_Set_1 <-> Q_Part_S
Q1:
LegoPart = LegoPart
Farve = Farve
Antal >= Antal
Answer : 16

Q2:
LegoPart = LegoPart
Farve = Farve
Antal < Antal
Answer: 5

Q3
LegoPart + Farve Not = LegoPart + Farve (not match for part and Colour)
or
T_Set_1 - Q1 -Q2
Answer: (111-16-5)

Once again.

Thanks

 

[Mihail]

In attachment you have Q1 and Q2.
For Q3 I need more time to find a way but, if you need only to count, Q1 and Q2 are enough.

 

[Steffe1611]

Hi Thanks to Drunkenneo

By adding in a reference for the part and one for colour Q1 and Q2 works perfectly and by the right joint I got the Q3

Perfect thanks.