Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Having Count Query ?

Posted on 2008-11-17
14
Medium Priority
?
422 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:szadroga
  • 6
  • 5
  • 3
14 Comments
 
LVL 120

Expert Comment

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

0
 

Author Comment

by:szadroga
ID: 22979290
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22979554
post the sql of the query that you are using
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 41

Expert Comment

by:Sharath
ID: 22980783
SELECT *
  FROM YourTable
 WHERE Week IN (SELECT Week FROM YourTable GROUP BY Week HAVING COUNT(Week) > 2)
0
 

Author Comment

by:szadroga
ID: 22984429
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22984475
is the query you posted above a saved query?

use the name of that query to replace the name of the table..
0
 

Author Comment

by:szadroga
ID: 22984525
Yes that is a saved query.  So I will use that query name as the table name in the query you posted?
0
 

Author Comment

by:szadroga
ID: 22984640
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22984767
post sample data from the TABLE..
0
 

Author Comment

by:szadroga
ID: 22984934
Sample data attached
Bad-Data.jpg
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22984984
would be better if i don't have to recreate the table.. what do you think?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22986594
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
 

Author Comment

by:szadroga
ID: 22987038
It is a much larger table than is displayed in that image.  That is just a snapshot of the data.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22987489
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question