How to filter for records with more than 1 entry? 1

[dkmidi]

Hi,

I have a table for Employees where the EmployeeID is sorted Ascending. How can i just have the EmployeeID records with more than one record shown? I don't want the EmployeeIDs grouped but need to see the unique entries in another Comments column. All I could think of was to Count it but then I couldn't specify ">1" in the criteria because of an error message saying it won't accept aggregates?

ie.From: EmployeeID
1
1
2
3
3
3

To: EmployeeID
1
1
3
3
3

And not: Employee ID
1
3

Thanks,

Derek

 

[dhookom]

You could create a query that groups by and counts EmployeeID from your first records. Set a criteria to include only records where the Count >1.

Then join this totals query with your first records joining the EmployeeID fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
SELECT A.*
FROM tblEmployees A
WHERE 1<(SELECT Count(*) FROM tblEmployees B WHERE B.EmployeeID=A.EmployeeID);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dkmidi]

I'm sorry PHV.. I'm a beginner at SQL. Where does table B come in because I only have 1 table.

SELECT tblEmployees *
FROM tblEmployees
WHERE 1<(SELECT Count(*) FROM tbl?? WHERE ??.EmployeeID=tblEmployees.EmployeeID);

I don't quite get where the tblB comes in.
Thanks for your help!

Derek

 

[SecondToNone]

Its just one table which is aliased as A and B...

-SecondToNone

 

[dkmidi]

Thanks for all the help! It works now. I couldn't understand the SQL to wrap it up nicely in 1 query so I created 2 queries as suggested by dhookom. Here's the resulting SQL from SQL view.

DQ_Filter Query 1:

SELECT T_Leaves.Staff_Id, Count(T_Leaves.LeaveTypeId) AS CountOfLeaveTypeId
FROM T_Leaves
GROUP BY T_Leaves.Staff_Id;


DQ_Filter Query 2:

SELECT T_Leaves.Staff_Id, T_Leaves.LeaveTypeId, [DQ_Filter Query 1].CountOfLeaveTypeId
FROM [DQ_Filter Query 1] INNER JOIN T_Leaves ON [DQ_Filter Query 1].Staff_Id = T_Leaves.Staff_Id
WHERE ((([DQ_Filter Query 1].CountOfLeaveTypeId)>1));

Thanks again!

Derek

 

[dhookom]

Try this solution which uses the subquery. Get used to the fact that you can include more than one "copy" of the same recordset in a query. In this query "A" is one instance of T_Leaves and "B" is another instance of the same table.

SELECT T_Leaves.*
FROM T_Leaves A
WHERE 1<(SELECT Count(*) FROM T_Leaves B WHERE B.Staff_Id=A.Staff_Id);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]