Multiple IDs for same Employee

[newbie123 via AccessMonster.com]

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

Is it possible to create the following tables in Access? If yes, then how?

Table1 has multiple/different EmpName for the same EmpID, and
different/multiple EmpID for the same EmpName. I would like to create a query:
Multiple Ids with Same Name

I am interested in the IDs only if they show up in Table 2.

****************

This is the code I used:

SELECT Table1.EMPNAME, Table1.EMPID, Table1.DEPT, Table1.MGRNAME
FROM Table1
WHERE (((Table1.EMPNAME) In (SELECT [EMPNAME] FROM [Table1] As Tmp GROUP BY
[EMPNAME] HAVING Count(*)>1 ))) and EMPID in (SELECT DISTINCT Table2.EMPID
FROM Table2)
ORDER BY Table1.EMPNAME;

HOWEVER, when putting in the previous I get the following error message:

You tried to execute a query that does not include the specified expression
'EmpName' as part of an aggregate function.

What does this mean?

 

[KARL DEWEY]

Try this --
SELECT Table1.EMPNAME, Table1.DEPT, Table1.MGRNAME, Table1.EMPID
FROM Table1 LEFT JOIN Table2 ON Table1.EMPID = Table2.EMPID
GROUP BY Table1.EMPNAME, Table1.DEPT, Table1.MGRNAME
HAVING Count(Table1.EMPID)>1;

 

[KenSheridan via AccessMonster.com]

Possibly this:

SELECT T1_1.*
FROM [Table 1] AS T1_1INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE
(SELECT COUNT(*)
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName) > 1;

or this:

SELECT T1_1.*
FROM [Table 1] AS T1_1INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE EXISTS
(SELECT *
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName
AND T1_2.EmpID <> T1_1.EmpID);

Ken Sheridan
Stafford, England

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

Is it possible to create the following tables in Access? If yes, then how?

Table1 has multiple/different EmpName for the same EmpID, and
different/multiple EmpID for the same EmpName. I would like to create a query:
Multiple Ids with Same Name

I am interested in the IDs only if they show up in Table 2.

****************

This is the code I used:

SELECT Table1.EMPNAME, Table1.EMPID, Table1.DEPT, Table1.MGRNAME
FROM Table1
WHERE (((Table1.EMPNAME) In (SELECT [EMPNAME] FROM [Table1] As Tmp GROUP BY
[EMPNAME] HAVING Count(*)>1 ))) and EMPID in (SELECT DISTINCT Table2.EMPID
FROM Table2)
ORDER BY Table1.EMPNAME;

HOWEVER, when putting in the previous I get the following error message:

You tried to execute a query that does not include the specified expression
'EmpName' as part of an aggregate function.

What does this mean?

 

[KenSheridan via AccessMonster.com]

Oops. Missed a space. Should have been:

Possibly this:

SELECT T1_1.*
FROM [Table 1] AS T1_1 INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE
(SELECT COUNT(*)
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName) > 1;

or this:

SELECT T1_1.*
FROM [Table 1] AS T1_1 INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE EXISTS
(SELECT *
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName
AND T1_2.EmpID <> T1_1.EmpID);

Ken Sheridan
Stafford, England

 

[newbie123 via AccessMonster.com]

Ken - I tried your suggestion but I got an error:

Syntax Error in JOIN operations.

Any suggestion?

Oops. Missed a space. Should have been:

Possibly this:

SELECT T1_1.*
FROM [Table 1] AS T1_1 INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE
(SELECT COUNT(*)
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName) > 1;

or this:

SELECT T1_1.*
FROM [Table 1] AS T1_1 INNER JOIN [Table 2]
ON T1_1.EmpID = [Table 2].EmpID
WHERE EXISTS
(SELECT *
FROM [Table 1] AS T1_2
WHERE T1_2.EmpName = T1_1.EmpName
AND T1_2.EmpID <> T1_1.EmpID);

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

I can't see any errors in the SQL statements as posted in my second post.
I've used the table and column names from your original post, so if the
actual table and/or column names differ from these then you should substitute
the real ones, remembering that any containing spaces or other special
characters mist be wrapped in square brackets [like this].

I did test the SQL as posted with a couple of tables of my own, changing the
table and column names to suit, and it worked fine.

If you have changed the SQL statements from those which I posted and you
still have problems post back the SQL you've used.

Ken Sheridan
Stafford, England

Ken - I tried your suggestion but I got an error:

Syntax Error in JOIN operations.

Any suggestion?

Oops. Missed a space. Should have been:

[quoted text clipped - 21 lines]

Ken Sheridan
Stafford, England