UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> returning top 25% results on grouped records, Office 2007    
 
   
pocoloco
post Aug 11 2010, 04:52 PM
Post #1

UtterAccess Enthusiast
Posts: 78
From: Overcrowded California



I am currently working on a query in which I need to return the top 25% for each cycle and book combinations. There are approximately 76 cycle and book combinations and I need to extract the top 25% for each.
For example:
Cycle 1
Book 1
250 records
Cycle 1
Book 5
500 records
Cycle 2
Book 13
100 records
Cycle 2
Book 20
600 records
Is it possible to do this in Access? Any suggestions or help would be greatly appreciated.
Thanks!!!!
Utterly confused iconfused.gif
Go to the top of the page
 
+
projecttoday
post Aug 11 2010, 11:25 PM
Post #2

UtterAccess VIP
Posts: 5,869
From: South Charleston, WV



I believe the only way to do this is to count the records and then input 25 % of the count into a SELECT TOP query. Here's and example that was posted yesterday. You can count the records with Dcount.
Go to the top of the page
 
+
John Spencer
post Aug 12 2010, 08:58 AM
Post #3

UtterAccess VIP
Posts: 2,550
From: Columbia, Maryland



Top 25% based on what? Return what? Could you expand a bit on what you are trying to do? I don't understand what you are attempting based on the limited sample data.
You can use a subquery to identify the TOP 25% in each cycle.
For instance, if you were looking for the TOP 25% of books sold for a cycle based on a date field.
Assumption: Your table has fields, Cycle, Book, DateOfSale, and Purchaser
Assuming that you want the most recent sales per combination of cycle and book
SELECT Cycle, Book, DateOfSale, Purchaser
FROM SomeQueryOrTable AS A
WHERE DateOfSaleIn (SELECT TOP 25 Percent DateOfSale FROM SomeQueryOrTable as B WHERE B.Cycle = A.Cycle AND B.Book = A.Book ORDER BY DateOfSale DESC)
Go to the top of the page
 
+
pocoloco
post Aug 12 2010, 02:52 PM
Post #4

UtterAccess Enthusiast
Posts: 78
From: Overcrowded California



Here is a better explanation of what data I am trying to extract from this query.
I am attempting to find the top 25% highest consumers for our utility customers based on figures from 2009. All of our customer accounts are broken up by cycle and books. Therefore, I need to extract the top 25% highest consumers broken down by cycle and book. Here are the fields that I currently have in the query that gives me the total consumption for each customer.
field 1 - Cycle
field 2 - Book
field 3 - Customer Account Number
field 4 - Customer Address
field 5 - Customer Total Consumption for 2009
field 6 - Customer Total Revenue for 2009
Since I have over 12,000 customers and approximately 76 cycle and book combinations, I really do not want to have to run multiple queries for each cycle and book. When I try to run the top 25% consumers it gives me the top 25% for the entire data set instead of giving me the top 25% grouped by cycle and book. Hope this helps clarify what data I need extracted from this query.
Thank you......
Joe
Go to the top of the page
 
+
John Spencer
post Aug 12 2010, 03:02 PM
Post #5

UtterAccess VIP
Posts: 2,550
From: Columbia, Maryland



The SQL for the query would look like the following.
SELECT A.*
FROM [Your Table Name] as A
WHERE [Customer Account Number] IN
(SELECT Top 25 Percent [Customer Account Number]
FROM [Your Table Name] as B
WHERE B.Cycle = A.Cycle
AND B.Book = A.Book
ORDER BY [Customer Total Consumption for 2009] DESC)
If that is not sufficient to help you, then post the query you now have that is giving you the top 25 percent overall. To do so open the query is SQL view (Design view then View: SQL), copy and paste the SQL string.
Go to the top of the page
 
+
projecttoday
post Aug 12 2010, 03:52 PM
Post #6

UtterAccess VIP
Posts: 5,869
From: South Charleston, WV



Thanks for correcting me on the "percent" key word which I was not familiar with.
Go to the top of the page
 
+
pocoloco
post Aug 18 2010, 03:20 PM
Post #7

UtterAccess Enthusiast
Posts: 78
From: Overcrowded California



This is the current SQL which returns the top 25% for the overall records. Please advise on how to manipulate the coding to allow for the top 25% per CycleBook.
SELECT TOP 25 PERCENT [TopUsersbyLocationAccount(3)Top25CycleBook].CycleBook, [TopUsersbyLocationAccount(3)Top25CycleBook].C_CUSTOMER, [TopUsersbyLocationAccount(3)Top25CycleBook].C_ACCOUNT, [TopUsersbyLocationAccount(3)Top25CycleBook].C_LASTNAME, [TopUsersbyLocationAccount(3)Top25CycleBook].C_FIRSTNAME, [TopUsersbyLocationAccount(3)Top25CycleBook].C_STREETNUM, [TopUsersbyLocationAccount(3)Top25CycleBook].C_STREETPREFIX, [TopUsersbyLocationAccount(3)Top25CycleBook].C_STREET, [TopUsersbyLocationAccount(3)Top25CycleBook].C_STREETSUFFIX, [TopUsersbyLocationAccount(3)Top25CycleBook].C_APT, [TopUsersbyLocationAccount(3)Top25CycleBook].C_ACCOUNTTYPE, [TopUsersbyLocationAccount(3)Top25CycleBook].TotalCons, [TopUsersbyLocationAccount(3)Top25CycleBook].TotalRev
FROM [TopUsersbyLocationAccount(3)Top25CycleBook];
Thank you,
Joe
Go to the top of the page
 
+
John Spencer
post Aug 18 2010, 03:41 PM
Post #8

UtterAccess VIP
Posts: 2,550
From: Columbia, Maryland



Try the following.
SELECT A.CycleBook
, A.C_CUSTOMER
, A.C_ACCOUNT
, A.C_LASTNAME
, A.C_FIRSTNAME
, A.C_STREETNUM
, A.C_STREETPREFIX
, A.C_STREET
, A.C_STREETSUFFIX
, A.C_APT
, A.C_ACCOUNTTYPE
, A.TotalCons
, A.TotalRev
FROM [TopUsersbyLocationAccount(3)Top25CycleBook] as A
WHERE C_Account IN
(SELECT Top 25 Percent [C_Account]
FROM [TopUsersbyLocationAccount(3)Top25CycleBook] as B
WHERE B.CycleBook = A.CycleBook
Orderllowing.
SELECT A.CycleBook
, A.C_CUSTOMER
, A.C_ACCOUNT
, A.C_LASTNAME
, A.C_FIRSTNAME
, A.C_STREETNUM
, A.C_STREETPREFIX
, A.C_STREET
, A.C_STREETSUFFIX
, A.C_APT
, A.C_ACCOUNTTYPE
, A.TotalCons
, A.TotalRev
FROM [TopUsersbyLocationAccount(3)Top25CycleBook] as A
WHERE C_Account IN
(SELECT Top 25 Percent [C_Account]
FROM [TopUsersbyLocationAccount(3)Top25CycleBook] as B
WHERE B.CycleBook = A.CycleBook
ORDER BY [TotalCons] DESC)
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 27th November 2014 - 08:20 PM