Using Subquery To Return Unique Rows..

[mmignot]

Hello,

I am trying to return from a query, a list of employees that
entered more than one timecard within the same reporting
period. Here is the query I'm trying to develop:

---------------------------------------------
SELECT a.LastName AS LastName,
a.FirstName AS FirstName
FROM Employee AS a
WHERE a.Active<>0
AND a.Employee_ID In
(SELECT b.Employee_ID
FROM Time_Card AS b
WHERE b.PeriodFrom = '02/04/2002'
AND b.PeriodTo = '02/10/2002')
ORDER BY a.LastName, a.FirstName;
---------------------------------------------
This is returning all of the employees. Can someone tell me what I need to do in order to just return the employees that
have more than one time in the same reporting period??

Thanks,
MM

 

[tlbroadbent]

You need to include an aggregate function to determine which employee have multiple cards.
[tt]
SELECT
a.LastName,
a.FirstName
FROM Employee AS a
WHERE a.Active<>0
AND b.PeriodFrom = '02/04/2002'
AND b.PeriodTo = '02/10/2002'
GROUP BY a.LastName, a.FirstName
HAVING Count(*)>1
ORDER BY a.LastName, a.FirstName[/tt] Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[mmignot]

Your suggestion worked Terry!!

Thanks so much!!
MM