• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Ranking Grouped Results In Access

Hello.
I am attempting to group a sales table on 2 fields, customer type and customer number. I want to sort by total sales dollars in descending order. I want to rank each customer number within a customer type grouping based on sales, with most sales ranked one. Any customer with a ranking over 10 is grouped together (all others) within that customer type. Is this possible without building separate tables for each customer type.
0
chantalcookware
Asked:
chantalcookware
  • 6
  • 4
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
a sample db with the table will help..
0
 
chantalcookwareAuthor Commented:
Its a single table. 4 fields. Attached sample. Thank you
db2.mdb
0
 
Rey Obrero (Capricorn1)Commented:
you uploaded a blank table ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
chantalcookwareAuthor Commented:
Attached has some sample records
db2.mdb
0
 
Rey Obrero (Capricorn1)Commented:
try this query


SELECT T.Cus_Type_Desc, T.Cus_No, T.Cus_Name, T.SumOfSls_Amt, (select count(*) from tblCusTypeReport1 t2 where t2.Cus_Type_Desc=t.Cus_Type_Desc and t2.SumOfSls_Amt>=t.SumOfSls_Amt) AS Ranking
FROM tblCusTypeReport1 AS T
ORDER BY T.SumOfSls_Amt DESC , T.Cus_Type_Desc;
0
 
Rey Obrero (Capricorn1)Commented:
test this

run query1 and query2
db2.mdb
0
 
ScriptAddictCommented:
I'm not sure if you need this to remain in the table, but if not this is extremely easy to do as a report.
0
 
chantalcookwareAuthor Commented:
A report is the end result I need. I am trying to open the above database and get an error saying it is untrusted or out of my intranet.
0
 
Rey Obrero (Capricorn1)Commented:

create this query and save as Query1

SELECT T.Cus_Type_Desc, T.Cus_No, T.Cus_Name, T.SumOfSls_Amt, (select count(*) from tblCusTypeReport1 t2 where t2.Cus_Type_Desc=t.Cus_Type_Desc and t2.SumOfSls_Amt>=t.SumOfSls_Amt) AS Ranking
FROM tblCusTypeReport1 AS T
ORDER BY T.SumOfSls_Amt DESC , T.Cus_Type_Desc;


then create this query

SELECT IIf([Ranking]>10,"Other",[Cus_Type_Desc]) AS CustType, Query1.Cus_No, Query1.Cus_Name, Query1.SumOfSls_Amt, Query1.Ranking
FROM Query1;

0
 
ScriptAddictCommented:
How to enable a disabled database
 
Office Access 2007 provides two ways to enable any disabled components. You can apply a digital signature to the database, or you can use the Trust Center to create a trusted location and then place the database in that trusted location. Note that some functions and expressions may still be disabled by Access sandbox mode.

The Access reporting functionality groups through the wizard
0
 
Rey Obrero (Capricorn1)Commented:
or you can do this


SELECT T.Cus_Type_Desc, T.Cus_No, T.Cus_Name, T.SumOfSls_Amt, (select count(*) from tblCusTypeReport1 t2 where t2.Cus_Type_Desc=t.Cus_Type_Desc and t2.SumOfSls_Amt>=t.SumOfSls_Amt) AS Ranking,
IIf((select count(*) from tblCusTypeReport1 t2 where t2.Cus_Type_Desc=t.Cus_Type_Desc and t2.SumOfSls_Amt>=t.SumOfSls_Amt)>10, "Other",[T].[Cus_Type_Desc]) as CustType
FROM tblCusTypeReport1 AS T
ORDER BY T.SumOfSls_Amt DESC , T.Cus_Type_Desc;
0
 
chantalcookwareAuthor Commented:
Perfect. Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now