Having Count Query ?
I have a data set that i need to query to return only the rows were a week # is listed more than two times (attached snapshot of the data). I am assuming I need to use a Having Count(Distinct) query, but i cannot get the logic correct.
From the data set JPEG, I only want the query to return the highlighted data because the week number is listed more than twice. For example, the query should only return the rows with the weeks 41 and 45 listed.
Data-Set.jpg
From the data set JPEG, I only want the query to return the highlighted data because the week number is listed more than twice. For example, the query should only return the rows with the weeks 41 and 45 listed.
Data-Set.jpg
ASKER
That query did not accomplish the proper data set. Not sure if this makes a difference, but I am doing this in Access and i went to the SQL View for queries and pasted that code. Replacing with the proper table name.
post the sql of the query that you are using
SELECT *
FROM YourTable
WHERE Week IN (SELECT Week FROM YourTable GROUP BY Week HAVING COUNT(Week) > 2)
FROM YourTable
WHERE Week IN (SELECT Week FROM YourTable GROUP BY Week HAVING COUNT(Week) > 2)
ASKER
Here is my current SQL statement that I am using.
This statement gives me the result set I posted yesterday.
This statement gives me the result set I posted yesterday.
SELECT [SZ qUtilization by Staff Type].Division, [SZ qUtilization by Staff Type].[Staff Type], [SZ qUtilization by Staff Type].Week, [SZ qUtilization by Staff Type].[Week Start]
FROM [SZ qUtilization by Staff Type]
GROUP BY [SZ qUtilization by Staff Type].Division, [SZ qUtilization by Staff Type].[Staff Type], [SZ qUtilization by Staff Type].Week, [SZ qUtilization by Staff Type].[Week Start]
HAVING ((([SZ qUtilization by Staff Type].[Week Start]) Not Like ("*/2009")))
ORDER BY [SZ qUtilization by Staff Type].[Staff Type];
is the query you posted above a saved query?
use the name of that query to replace the name of the table..
use the name of that query to replace the name of the table..
ASKER
Yes that is a saved query. So I will use that query name as the table name in the query you posted?
ASKER
Still no luck. The name of the saved query is 'SZ qBad Data'. Here is the query i tried:
select [division],[staff type],[week],[week start]
from [SZ qBad Data]
where [SZ qBad Data].week in(select [week] from [SZ qBad Data] as X group by [week] having count(*)>2)
post sample data from the TABLE..
ASKER
Sample data attached
Bad-Data.jpg
Bad-Data.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just run this query and check whether it displays records having weeks 46,47 and 51
select [week] from [SZ qBad Data] group by [week] having count(*)>2
ASKER
It is a much larger table than is displayed in that image. That is just a snapshot of the data.
what i am telling is if your inner query is returning some result then the outer query should give you the result. Its pretty simple query.
from tablex
where tablex.week in(select [week] from tablex as X group by [week] having count(*)>2)