Combine two tables with unique values 2

[stevedemo]

I have two tables Table1 & table2.

Table1 is a list of part numbers table2 is a list of possible locations.

I would like to create a table by combining them with the locations and only the parts from table1. However when I do this I am getting all the possible locations for table2.
In excell I would use a Vlookup and after I pasted the unique values. How do I accomplish this in access ?

Table 1 Part
Part1
Part2

Table 2 Part Location
Part1 A1
Part2 B3
Part1 Gt
Part1 X4
Part4 Gr
Part7 Xx

Table 3 Part Location
Part1 A1
Part2 B3


All I want is the chance to prove money won't make me happy.

 

[PHV]

A starting point (SQL code)

SELECT T1.Part, Min(T2.Location)
FROM [Table 1] T1 INNER JOIN [Table 2] T2 ON T1.Part = T2.Part
GROUP BY T1.Part

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I'm not sure how familiar you are with the query designer.... If you are a little familiar with it, this will hopefully fill in the blanks, otherwise let me know.

Vlookup in Excel is a type of join in Access. It is an Outer join. In Access a join is the match and you can have as many columns/fields from tables as you want.

A join is used in a query. A vlookup is an outer join.

In the query designer you make a join by dragging and dropping a field from one table on to the field of another. The default is an inner join which returns values from the tables ONLY where there are matches. I belive this is what you want. However if you double click the line that appears you can change it to an outer join that behaves like Vlookup.

Once you have your join, you can double click fields from the tables to add them to the query.

 

[lameid]

PH and I cross posted... You notice in the SQL statment posted that it uses an inner join to match data between the tables. PH also added grouping to remove duplicates and is returning the minimum location. This may be similar to what you are after but I think you had a problem caused by not having a join at all and do not need grouping.

 

[PHV]

My SQL code should produce the result asked for Table 3 ...

 

[lameid]

Hmmm... I didn't notice the first only value in the result...

I forgot the weakness of Vlookup... it only returns the first match not all matches so the direction of the lookup matters in Excel. That suggests the grouping and the minumum I wanted to take out of PHV's solution belongs. I am having a hard time seeing how it is useful but it is equivalent.

 

[stevedemo]

PHV,

Good call I see where you are going with this however when I try to add the Min function I get the error that "T1" is not part of the aggergate function. I built a diffrent query to test the min function and it produced the same error, suggestions ?

All I want is the chance to prove money won't make me happy.

 

[lameid]

stevedemo,

Please post your SQL.

 

[stevedemo]

Here is what I have, it should only produce 42 records but I get 77.

SELECT Totals2_ftgcrimp.Comp, Totals2_ftgcrimp.SumOfTotal_Reqd, Zrloc2.Location
FROM Totals2_ftgcrimp LEFT JOIN Zrloc2 ON Totals2_ftgcrimp.Comp = Zrloc2.Item_Number;

All I want is the chance to prove money won't make me happy.

 

[stevedemo]

I get this:

Comp SumOfTotal_Reqd Location
1A10 526 zr010204
1A10 526 ZR030606
1A10 526 zr030401
1A12 598 zr040601
1A16 245 zr040301
1A4 3544 zr010403
1A4 3544 zr010703
1A6 7608 zr040801
1A6 7608 zr030503
1A6 7608 zr030505

Where I should be getting this

Comp SumOfTotal_Reqd Location
1A10 526 zr010204
1A12 598 zr040601
1A16 245 zr040301
1A4 3544 zr010403
1A6 7608 zr030505


All I want is the chance to prove money won't make me happy.

 

[lameid]

Every field must be part of an aggregate funtion, either be in the group by or have an aggregate function used on it. Technically you could also use literals, parameters or expressions using aggregate or grouped fields.

SELECT Totals2_ftgcrimp.Comp, Totals2_ftgcrimp.SumOfTotal_Reqd, Min(Zrloc2.Location) As Location
FROM Totals2_ftgcrimp LEFT JOIN Zrloc2 ON Totals2_ftgcrimp.Comp = Zrloc2.Item_Number
Group By Totals2_ftgcrimp.Comp, Totals2_ftgcrimp.SumOfTotal_Reqd;

 

[stevedemo]

Lameid,

That works, no errors. I will take a closer look at this tomorrow.

Thanks,

Steve

All I want is the chance to prove money won't make me happy.