Same Name with Multiple IDs

[newbie123 via AccessMonster.com]

I previously posted this request. Got part of my question answered but
because it's an old request, I thought to re-post it. Sorry if this is not
protocol.

Table 1 - Each record has the following fields:
1. EmpId
2. Dept
3. EmpName
4. MgrName

Table 2 - Each record has the following fields:
1. EmpId
2. Units Worked
3. Product Type

Table1 has multiple/different EmpName for the same EmpID, and
different/multiple EmpID for the same EmpName. I would like to create a query:
Same Name with Multiple IDs. I am interested in the IDs only if they show up
in Table 2.

 

[KARL DEWEY]

Try these --
SELECT Table1.FullName AS Name_
FROM Table1
GROUP BY Table1.FullName
HAVING (((Count(Table1.ID))>1));

SELECT Table2.ID
FROM (Table1_A INNER JOIN newbie1 ON Table1_A.Name_ = Table1.FullName) INNER
JOIN Table2 ON Table1.ID = Table2.ID;

 

[newbie123 via AccessMonster.com]

What does "A" represent?

Try these --
SELECT Table1.FullName AS Name_
FROM Table1
GROUP BY Table1.FullName
HAVING (((Count(Table1.ID))>1));

SELECT Table2.ID
FROM (Table1_A INNER JOIN newbie1 ON Table1_A.Name_ = Table1.FullName) INNER
JOIN Table2 ON Table1.ID = Table2.ID;

I previously posted this request. Got part of my question answered but
because it's an old request, I thought to re-post it. Sorry if this is not

[quoted text clipped - 15 lines]

Same Name with Multiple IDs. I am interested in the IDs only if they show up
in Table 2.

 

[John Spencer]

This query should return all EmpID in Table1 that have two or more different
empName values (except it won't detect nulls in EmpName.) If that is
important, then you will need to change the query to account for the nulls.

SELECT Table1.EmpID
FROM Table1 INNER JOIN Table2
ON Table1.EmpID = Table2.EmpID
GROUP BY Table1.EmpID
HAVING Min(Table1.EmpName) <> Max(Table1.EmpName)

If you need the entire Record then

SELECT Table1.*
FROM Table1
WHERE EmpID in
(SELECT Table1.EmpID
FROM Table1 INNER JOIN Table2
ON Table1.EmpID = Table2.EmpID
GROUP BY Table1.EmpID
HAVING Min(Table1.EmpName) <> Max(Table1.EmpName))

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

 

[KARL DEWEY]

Table1_A is the name I gave to the first query.
--
Build a little, test a little.

What does "A" represent?

Try these --
SELECT Table1.FullName AS Name_
FROM Table1
GROUP BY Table1.FullName
HAVING (((Count(Table1.ID))>1));

SELECT Table2.ID
FROM (Table1_A INNER JOIN newbie1 ON Table1_A.Name_ = Table1.FullName) INNER
JOIN Table2 ON Table1.ID = Table2.ID;

I previously posted this request. Got part of my question answered but
because it's an old request, I thought to re-post it. Sorry if this is not

[quoted text clipped - 15 lines]

Same Name with Multiple IDs. I am interested in the IDs only if they show up
in Table 2.