join with duplicates

[Steve]

Table1 has DeptID and LOB (line of business). Table2 has DeptID, name, cost
center, job title.

More than one person can share the same DeptID in Table2.

I want to join Table1 and Table2. I want all records in Table1 and only
matches in Table2. But I don't want duplicate matches in Table2. If two or
more people have the same DeptID in Table2, I consider it a duplicate. Not
all fields have to be the same. I don't care which match I get...the first,
last or whatever.

Can't figure this out. I thought I was fairly savvy but it's stumping me.

 

[Roger Carlson]

Well, this would work:

SELECT Table1.DeptID, Table1.LOB, T2.Fname, T2.[Fcost center], T2.[Fjob
title]
FROM Table1 INNER JOIN (SELECT Table2.DeptID, First(Table2.name) AS Fname,
First(Table2.[cost center]) AS [Fcost center], First(Table2.[job title]) AS
[Fjob title]
FROM Table2
GROUP BY Table2.DeptID) as T2 ON Table1.DeptID = T2.DeptID;

Unfortunately, if you ever opened this query in the Access Query Builder, it
would screw it up. The QB doesn't like Select statements in the FROM clause
much. You could do it if you got rid of the spaces in your field names like
this:

SELECT Table1.DeptID, Table1.LOB, T2.Fname, T2.Fcostcenter], T2.Fjobtitle
FROM Table1 INNER JOIN [SELECT Table2.DeptID, First(Table2.name) AS Fname,
First(Table2.costcenter) AS Fcostcenter, First(Table2.jobtitle) AS Fjobtitle
FROM Table2
GROUP BY Table2.DeptID]. As T2 ON Table1.DeptID = T2.DeptID;

The Query Builder is more friendly to this syntax, but you can't have any
other brackets in your query. That's why you have to get rid of the spaces.
The dot (.) after the final bracket is manditory.

However, it might be easier to understand if you broke it into two queries:

qryT2:
SELECT Table2.DeptID, First(Table2.name) AS Fname, First(Table2.[cost
center]) AS [Fcost center], First(Table2.[job title]) AS [Fjob title]
FROM Table2
GROUP BY Table2.DeptID

qryFinal:
SELECT Table1.DeptID, Table1.LOB, qryT2.Fname, qryT2.[Fcost center],
qryT2.[Fjob title]
FROM Table1 INNER JOIN qryT2 ON Table1.DeptID = qryT2.DeptID;


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

You might try

SELECT Table1.DeptID, LOB
, FIRST([Name])
, First([Cost Center])
, FIrst([Job Title])
FROM Table1 LEFT JOIN Table2
ON Table1.DeptID = Table2.DeptID
GROUP BY Table1.DeptID, LOB

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County