SQL Query to find records with a certain number of recurrances

[chrispchenry]

Hello everyone,
Got my third, and hopefully final question and I'll be done this program.

Everytime someone shows up late, a record is made in the database with the Time, Date and Employee number. I've been using crystal reports 4.0 I think. I can report lates for each employee, as well as every late recorded into the system. Currently, there are about 630 of them.

What kinda of SQL query would I use to return the records that have a reoccuring value (the employee number) equal/more than a number?

So, if Joe Blow showed up late 5 times, and John Q showed up late 8 times, the respective IDs are stored. How would I search for people who've showed up late 4 times, get there IDs returned and print a report of ALL the lates, showing all the infractions?

Originally I was going to add an extra field into the Employee Info table with a record to store the number of lates. It'd be updated each time they showed up late.

 

[Terpsfan]

I think you need to use a subquery to get this done...this should work.

SELECT Empnum,name,time FROM tblEmployee WHERE (SELECT Count(late) AS LateCount
FROM tblEmployee
WHERE tblEmployee.late=True
GROUP BY tblEmployee.empnum
HAVING Count(Late) > 4

 

[Terpsfan]

I had put a Late column in the table as a yes/no field, but in the query you use true false.

 

[PraveenMenon]

select empname,count(*) from empLate where count(*)>4 group by empname order by count(*) All the Best
Praveen Menon
pcmin@rediffmail.com

 

[chrispchenry]

Omega: i'm still working on implementing yours, its a little more involved than I hoped it would be.

Menon: sorry, but it didn't work. You can't use an aggregate function in the WHERE clause of an SQL statement.

Still working' away, thanks!
cpch

 

[chrispchenry]

Blast. I just realised that I need the SQL code for the selection formula to run it through Crystal Reports. The syntax that's been suggest, as well as what I've been doing, are wrong.

So...I need the Crystal Reports SQL syntax.

More work for me,
cpch

 

[SemperFiDownUnda]

I would do it this way

SELECT E.Emp_ID, E.Emp_Name, COUNT(*) As FreqLate
FROM Employee E
INNER JOIN LateTable LT
ON E.Emp_ID = LT.EmpID
GROUP BY E.Emp_ID, E.Emp_Name
HAVING COUNT(*) > 4