List ID's with multiple values in same column

[russmann2000]

I have a table with EMPLOYEE_IDs and a column (EMPLOYEE_CODE) with more than one value for the same EMPLOYEE_ID. I'm attempting to pull all of the ID's with more than one EMPLOYEE_CODE, can get it to list the ID's, but if I try to add the CODE I get nothing. This is what I have if I just want the ID.

SELECT EMPLOYEE_ID
FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE In ("CODE 1","CODE 2")
GROUP BY EMPLOYEE_ID
HAVING Count(EMPLOYEE_ID) >1

How can I get it to list the EMPLOYEE_CODEs as well?

 

[plog]

I don't think that SQL works. For every field in the SELECT it must either be in the GROUP BY or be contained within an aggregate function (SUM, COUNT, MAX, etc) in the SELECT. You have one field in your SELECT and another in the GROUP BY. That should not work.

Now, I understand your issue, but I don't understand what you ultimately want. Could you please demonstrate it with data? Let's use the below data set as what's in your table:

tblEmployee
EMPLOYEE_ID, EMPLOYEE_CODE
1, CODE 1
1, CODE 2
1, CODE 3
2, CODE 1
3, CODE 3
3, CODE 4
4, CODE 1
4, CODE 3
5, CODE 1
5, CODE 1

Using that data, show me what data you expect the query to return. No need for any explanation, just show me what data you expect.

 

[russmann2000]

I edited. I want the ID and the code.

I want all of the IDs with both CODE 1 and CODE 2.

1, Code 1
1, Code 2

And so on.

If I do like the code on the original post it lists all of the IDs just fine, but if I try to get it to list the codes as well, I get nothing.

 

[theDBguy]

I edited. I want the ID and the code.

I want all of the IDs with both CODE 1 and CODE 2.

1, Code 1
1, Code 2

And so on.

If I do like the code on the original post it lists all of the IDs just fine, but if I try to get it to list the codes as well, I get nothing.

Hi. This is very possible but it's not quite clear what your goal is. Can you please post more data than what you already showed us? In your original SQL/query, you said it accurately gives you the IDs with both Code1 and Code2 but couldn't list the codes with the ID. However, we know those IDs have both Code1 and Code2, so there's really no need to list them, is there? However, if you're saying an ID with both Code1 and Code2 could also have Code3 and you want to see it listed, then that's something you have to tell us. In the end, whatever it is you're trying to do, I'm pretty sure we'll be able to show you how to do it. We'll just need to make sure we understand what it is first.

 

[russmann2000]

I want to list both the ID, and the CODE. It’s possible to have more codes, but I only want CODE 1 and CODE 2.

If I use the sql in the original post, it gives me just the ID (obviously). I know it’s correct because I already know which IDs have which codes.

It’s obviously necessary, I wouldn't ask otherwise.

I’ve tried to get it to display the Codes as well but I get 0 records.

 

[Pat Hartman]

You can use the query wizard to build a find duplicates query. That may be what you want.

 

[June7]

Try:

SELECT tblEmployee.* FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE IN("CODE 1", "CODE 2") AND EMPLOYEE_ID IN (
SELECT EMPLOYEE_ID
FROM tblEMPLOYEE
WHERE EMPLOYEE_CODE In ("CODE 1","CODE 2")
GROUP BY EMPLOYEE_ID
HAVING Count(*) >1);

Or try INNER JOIN your first query to tblEmployee.

 

[theDBguy]

I want to list both the ID, and the CODE. It’s possible to have more codes, but I only want CODE 1 and CODE 2.

If I use the sql in the original post, it gives me just the ID (obviously). I know it’s correct because I already know which IDs have which codes.

It’s obviously necessary, I wouldn't ask otherwise.

I’ve tried to get it to display the Codes as well but I get 0 records.

Hi. Thank you for the clarification. We know it is necessary or you wouldn't be asking for it. We just couldn't understand what it was you wanted. Now that you've given us more information, please try June7's suggestion and let us know how it goes. Cheers!

 

[russmann2000]

Thanks so much. That solved it.

I tried a similar subquery earlier, just didn't do it correctly.

I need to step away from the code sometimes, this was way simple and should have caught it. :banghead:

 

[arnelgp]

much simpler:

SELECT employees.Employee_ID, employees.Employee_Code
FROM employees
GROUP BY employees.Employee_ID, employees.Employee_Code
HAVING (((employees.Employee_Code)<"Code 3") AND ((Count("1"))<3))
ORDER BY employees.Employee_ID, employees.Employee_Code;