Hello All,

I want to have a field on the report which ranks by the sales of each
wholesaler. However, I cannot use the Running Sum because I am not
sorting it by the sales, but by wholesalers' Name.

I tried to create a subreport with the Running sum and order by the
sales (which come up correctly with the rank), and have Masterlink &
Childlink by the name, and then create a field on Main Report to call
the rank field from the subreport. However, I got: #Name?

Is there any way to rank the sales but sort by name?

Your help is greatly appreciated!!

You could create a ranking query to give you the rank. Or you could use
DCount against a query that has the sales values and the wholesalers name.

Is there any chance you can post the data source of your report? I would
assume that it is a query and you can copy and paste the SQL view of the query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 3/9/2011 5:11 PM, Orchid wrote:

On Mar 10, 8:20*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
John,
Thanks so much for your response!
Dcount counts how many records of the table. However, I want to rank
by Current month Sales number . For instance, the wholesaler in a
group with highest sales will be ranked 1, the 2nd highest will be
ranked 2, and so on... It will be the same for the other groups with
in the same query. and order by wholesalers' name. The result would be
as follow:

Name Rank Sales
Brian Smith 6 $2,200,000
Jeff Wong 1 $5,000,000
John Doe 2 $4,230,000
........
..... and so on..

Is there any way to get the result? Thanks in advance!!

Orchid wrote:
Have you thought about ties?

Assuming those sales are aggregated from detail records, you will first need
either a saved grouping query, or, if there are so many records that the
grouping query performs slowly, a work table to store the results from the
grouping query. I will assume that performance is not a problem. Create a
saved query called GroupedSales (this is a good time to get you acquainted
with SQL View - switch a new query to SQL View and paste this in, fixing the
field and table names):

SELECT [Name], Sum(Sales) As TotalSales
FROM sourcetable
GROUP BY [Name]

Then create this saved query that generates the rank numbers (call it
RankedSellers):

SELECT [Name],[TotalSales],
(SELECT Count(*) FROM GroupedSales
WHERE [TotalSales]<=g.[TotalSales]) AS SalesRank
FROM GroupedSales AS g

Use RankedSellers as the source of the report.

On Mar 10, 11:50*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Thanks for your response Bob!
Sorry I didn't explain clear enough. I have different groups of
wholesalers who sale different products. On the report, I want to rank
the sales (highest sales will be ranked as 1) within a group with the
result as follows:

Group Name Rank Sales
AN Brian Smith 6 $2,000,000
AN Jeff Wong 1 $5,000,000
AN John Doe 2 $4,000,000
..........
MF Adam Allen 8 $2,000,000
MF Bryan Hays 3 $12,000,000
MF Scott Hays 6 $3,000,000

Etc....

If there are 20 wholesalers in Group AN, will be ranked 1 to 20, and
it will be starting from 1 for Group MF (it will be 1 to 25 if there
are 25 of them). And starts as 1 again for another group, and so on.

BTW, the query runs very slow. Is it because of complicate
calculation?
Thanks for your help!!

Orchid wrote:

I created a table; ID, Cname, Csales. I entered Pat (100), Mike (200),
Sam (300).

I then created a query that selected all recs from Table1 and sorted on
Csales decending. (You would modify to sort by category, sales)

I then created a report. I used the query as my recordsource. I added
a textbox called Rank and it had the following ControlSource
=GetRank([ID])

GetRank looked like this
Private Function GetRank(ID As Long) As Integer
Dim r As Recordset
Set r = CurrentDb.OpenRecordset("Query1")
r.FindFirst "ID1 = " & ID
GetRank = r.AbsolutePosition + 1
r.Close
Set r = Nothing
End Function

Then I went to Sorting and Grouping and added a sort on CSales descending.

For 3 records the result was instantaneous. I assume it'd still be
quick with more records.

If you did this, you'd pass both the ID and the category to GetRank.
And instead of entering Query1 as the recordsource, it might be
something like
Private Function GetRank(ID As Long, Cat as Long) As Integer
Dim r As Recordset
Dim s as string
s = "Select * from Query1 Where Cat = " & Cat & _
" Order CSales DESC"
Set r = CurrentDb.OpenRecordset(s)
r.FindFirst "ID = " & ID & "And Cat = " & Cat
GetRank = r.AbsolutePosition + 1
r.Close
Set r = Nothing
End Function

If the report is sorted on Name (via Sorting and Grouping), the above
function selects/filters the records to the category and uses the
recordset's absolute postition to return the ranking for that cust
within that category.

I think it'd be quicker.

On Mar 10, 7:02*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
your response is greatly appreciated!

It looks like I would need to use the Function to get both ID & cat.
However, I have problem to substitute my really fields into your
Function.
What data would it be for the field "ID" on the table?
Should we have another field "Cat" on the table also?

Thanks in advance!!

Orchid wrote:
Not sure. It's been a bit.

In my example I had a table that had a primary key. I assume your's
does as well. ID most likely in EmpID, an autonumber or long number.
Cat was simply your column "Group" or whatever field you used.

I was simply providing a method or concept.

I might try my simple example out and see if it works like you want. If
so, modify to suit.