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
szadrogaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
select division,stafftype,[week],weekstart
from tablex
where tablex.week in(select [week] from tablex as X group by [week] having count(*)>2)

0
szadrogaAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
post the sql of the query that you are using
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SharathData EngineerCommented:
SELECT *
  FROM YourTable
 WHERE Week IN (SELECT Week FROM YourTable GROUP BY Week HAVING COUNT(Week) > 2)
0
szadrogaAuthor Commented:
Here is my current SQL statement that I am using.  

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];

Open in new window

0
Rey Obrero (Capricorn1)Commented:
is the query you posted above a saved query?

use the name of that query to replace the name of the table..
0
szadrogaAuthor Commented:
Yes that is a saved query.  So I will use that query name as the table name in the query you posted?
0
szadrogaAuthor Commented:
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)

Open in new window

0
Rey Obrero (Capricorn1)Commented:
post sample data from the TABLE..
0
szadrogaAuthor Commented:
Sample data attached
Bad-Data.jpg
0
Rey Obrero (Capricorn1)Commented:
would be better if i don't have to recreate the table.. what do you think?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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

Open in new window

0
szadrogaAuthor Commented:
It is a much larger table than is displayed in that image.  That is just a snapshot of the data.
0
SharathData EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.