Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-27-2015, 07:14 AM   #1
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 172
Thanks: 3
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
I need to get the Top 10 per State and County

Hello Everyone,
I am trying to get the Top 10 NPI's by State and By County based on the Current_Year_Members_Outreached. Below is the query I created but I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever. My source table only has 67k records which doesn't seem like a lot. So, I have two questions:
1. Does my query below look correct based on what I am trying to achieve?
2. Is there a better way to right this query to get better performance?


As always any help is greatly appreciated....



Code:
 
SELECT
A.STATE,
A.COUNTY,
A.NPI,
A.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS A
WHERE A.[SumOfCurrent Year Members Outreached] =
(
SELECT TOP 10
B.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS B
WHERE A.STATE = B.STATE AND A.COUNTY = B.COUNTY AND A.NPI = B.NPI
ORDER BY
B.[SumOfCurrent Year Members Outreached] DESC
)

bconner is offline   Reply With Quote
Old 02-27-2015, 07:21 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: I need to get the Top 10 per State and County

Does your query do what you want? Are the results what you expected?
It's easy to test. And it's all read only.
You can always test on a smaller file.

For more info see top N at this link
and this one which deals with speed
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 02-27-2015 at 07:26 AM.
jdraw is offline   Reply With Quote
Old 02-27-2015, 07:25 AM   #3
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 172
Thanks: 3
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
Re: I need to get the Top 10 per State and County

jdraw,
I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever.

bconner is offline   Reply With Quote
Old 02-27-2015, 07:29 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: I need to get the Top 10 per State and County

Why not create a copy of the query but limit it with some constraint to give fewer records for testing?
No need to do your testing on the full dataset. Get a test working well, then move to your full data.

Can you post a copy of the database --remove anything private/confidential, then zip?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-27-2015, 07:38 AM   #5
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 172
Thanks: 3
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
Re: I need to get the Top 10 per State and County

Ok, I have attached a copy of the database. The query I am referring to is named Qry_Provider_Top_10.
bconner is offline   Reply With Quote
Old 02-27-2015, 07:40 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: I need to get the Top 10 per State and County

Nothing attached.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-27-2015, 07:48 AM   #7
bconner
Newly Registered User
 
Join Date: Dec 2008
Posts: 172
Thanks: 3
Thanked 0 Times in 0 Posts
bconner is on a distinguished road
Re: I need to get the Top 10 per State and County

Sorry, here ya go....
Attached Files
File Type: zip PEC_Reporting1.zip (1.47 MB, 30 views)

bconner is offline   Reply With Quote
Old 02-27-2015, 09:44 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: I need to get the Top 10 per State and County

bconner,

I really don't understand your data. From my look there seems to be 850+ records in
Qry_Provider_Distinct that have no data in Contracted State???


I do not understand your qry_Provider_Distinct query and all of the aggregate values
Code:
SELECT Provider_Level.State, Provider_Level.County, Provider_Level.NPI
, Sum(Provider_Level.[Current Year Members Outreached]) AS [SumOfCurrent Year Members Outreached]
, Max(Provider_Level.[Address 1]) AS [MaxOfAddress 1]
, Max(Provider_Level.[Address 2]) AS [MaxOfAddress 2], Max(Provider_Level.City) AS MaxOfCity, Max(Provider_Level.State) AS MaxOfState
, Max(Provider_Level.[Zip Code]) AS [MaxOfZip Code]
, Max(Provider_Level.[Phone Number]) AS [MaxOfPhone Number]
FROM Provider_Level
GROUP BY Provider_Level.State, Provider_Level.County, Provider_Level.NPI
ORDER BY Sum(Provider_Level.[Current Year Members Outreached]) DESC;

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 02-27-2015 at 09:57 AM.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Bristol County, MA Dick7Access Politics & Current Events 4 07-20-2014 12:42 AM
Normalizing various city/county/state relationships (w/out zip) DorkyDuvessa Theory and practice of database design 3 07-01-2011 10:58 AM
Question Searching by County/Region/Country etc How? CarysW General 2 06-19-2009 08:39 AM
Left Join query on form shows null state on chkbox and blank state on query Fernando Forms 7 02-22-2008 12:30 PM




All times are GMT -8. The time now is 06:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World