Displaying Details if Count > parameter

[Guest]

How do I display the details of each record if the count of each
ExceptionType is greater than, let say 3, within a certain period? I would
like to know the reason for each exception if the total number of exceptions
within a period is greater than 3. Here is the SQL:

SELECT DISTINCT Name,Count(TypeID) AS Num
FROM tblException
WHERE PostDate>=DateAdd('d',-190,Date())
GROUP BY Name,TypeID
HAVING Count(TypeID)>=3

But this only gives me the total count. If I add Reason to the query, I get
no record at all. Do I need multiple queries / nested queries, or change my
GROUP BY / HAVING?

 

[Marshall Barton]

How do I display the details of each record if the count of each
ExceptionType is greater than, let say 3, within a certain period? I would
like to know the reason for each exception if the total number of exceptions
within a period is greater than 3. Here is the SQL:

SELECT DISTINCT Name,Count(TypeID) AS Num
FROM tblException
WHERE PostDate>=DateAdd('d',-190,Date())
GROUP BY Name,TypeID
HAVING Count(TypeID)>=3

But this only gives me the total count. If I add Reason to the query, I get
no record at all. Do I need multiple queries / nested queries, or change my
GROUP BY / HAVING?

Try removing TypeID from the Group By clause.

 

[John Spencer]

What is REASON? Where is it located (a field in the same table)?

PErhaps the following:

SELECT TE.Name, TE.REASON, TblCount.Num, TE.TypeID
FROM tblException INNER JOIN
( SELECT Name, Count(TypeID) AS Num
FROM tblException as TE
WHERE PostDate>=DateAdd('d',-190,Date())
GROUP BY Name, TypeID
HAVING Count(TypeID)>=3 ) as TblCount
ON TE.Name = TblCount.Name AND
TE.TypeID = TblCount.TypeID
WHERE PostDate>=DateAdd('d',-190,Date())

 

[Guest]

I tried that but this still doesn't solve the problem. I want to see each of
the occurences where the _total_ count of Type >=3. If I have:

Name Type Reason PostDate
A T1 text1 8/5/06
B T1 text 8/1/06
A T1 text2 7/3/06
B T2 text3 6/25/06
A T1 text4 6/20/06
A T3 text5 7/20/06

I would like to see the reasons and dates for A's T1 activities. This
doesn't appear to be a difficult request but I 've just been going round and
round on it. Thanks for helping.

 

[Guest]

I keep getting the "syntax error in join operation" error. It's either
pointing to the TE in TE.Name / TE.Reason or the TE in "tblException as TE".
Can't figure out why. Seems like it should work.

 

[Marshall Barton]

If you want to see all the records for a name and type that
occur 3 or more times, try something more like:

SELECT T.[Name], T.TypeID, T.Reasonm T.PostDate
FROM tblException As T
WHERE PostDate>=DateAdd('d',-190,Date())
AND (SELECT Count(TypeID)
FROM tblException As X
WHERE PostDate>=DateAdd('d',-190,Date())
AND X.[Name] = T.[Name]
AND X.TypeID = T.TypeID) >=3

 

[John Spencer]

My error. I used the wrong table name.

SELECT TE.Name, TE.REASON, TblCount.Num, TE.TypeID
FROM tblException as TE INNER JOIN
( SELECT TE1.Name, Count(TE1.TypeID) AS Num
FROM tblException as TE1
WHERE TE1.PostDate>=DateAdd('d',-190,Date())
GROUP BY TE1.Name, TE1.TypeID
HAVING Count(TE1.TypeID)>=3 ) as TblCount
ON TE.Name = TblCount.Name AND
TE.TypeID = TblCount.TypeID
WHERE PostDate>=DateAdd('d',-190,Date())

 

[Guest]

Other than needing to add TE1.TypeID to inner SELECT, this works! Thank you
for taking the time.

 

[Guest]

Worked beautifully! And concise, too. Thank you very much.

If you want to see all the records for a name and type that
occur 3 or more times, try something more like:

SELECT T.[Name], T.TypeID, T.Reasonm T.PostDate
FROM tblException As T
WHERE PostDate>=DateAdd('d',-190,Date())
AND (SELECT Count(TypeID)
FROM tblException As X
WHERE PostDate>=DateAdd('d',-190,Date())
AND X.[Name] = T.[Name]
AND X.TypeID = T.TypeID) >=3
--
Marsh
MVP [MS Access]

I tried that but this still doesn't solve the problem. I want to see each of
the occurences where the _total_ count of Type >=3. If I have:

Name Type Reason PostDate
A T1 text1 8/5/06
B T1 text 8/1/06
A T1 text2 7/3/06
B T2 text3 6/25/06
A T1 text4 6/20/06
A T3 text5 7/20/06

I would like to see the reasons and dates for A's T1 activities. This
doesn't appear to be a difficult request but I 've just been going round and
round on it.