Marshall,
I agree in principle. What I failed to ask in my earlier post was whether
the OP needed this for a report, or wanted it for sorting in a form or query
that was going to be used in some other way.
1. If ClRank is being used in a report, then I would set the group and sort
options so that the results come out in the proper order, and then use an
unbound text box, with a Control Source: =1, and the Running Sum property
set to "Over Group".
2. But if you need to use this field in some other way (graphs, crosstab
queries, aggregate queries where you only want the top five for each [Sold
To]) or have a large dataset, or use that field alot, then I have found that
it is much quicker to run the code to adjust the rank than it is to do the
subquery over and over again. What I failed to notice earlier is that the
SQL statement the OP used to create his recordset does not have the Order By
clause correct. I think what he wants is:
Dim strSQL as string
strSQL = "SELECT * FROM [Sales no1] Order by [Sold to], Amt DESC"
set rs = db.openrecordse(strSQL)
To test my theory, I:
1. created a table with four columns (ID,[Sold To], Amt, Rank).
2. I then created a couple of queries, one that computs the rank on the fly
(same SQL as you provided) and a second that returns the records ordered by
[Sold To] (asc) and Amt (desc).
3. I then created a procedure that did the following four steps:
a. Delete all the records in tbl_Sales and generate a new set of
records based on a number passed to the routine. Basically, this selected
one of 10 values for [Sold To] from a table based on a random number, then
generated a value between 0 and 1,000,000 and inserted these values into
tbl_Sales
b. Opened a recordset using your SQL, move to the last record, display
the elapsed time (using the Timer function) from just before opening the
recordset until immediately after the rs.movelast
c. Run my code to compute the Rank, again measuring the time from just
before opening the recordset to just after dropping out of the While not
rs.eof: rs.movenext: Wend loop
d. Opened a recordset using the second query mentioned above, and
display the elapsed time as in step 3.b.
4. My results were as follows:
Call GenSales(100)
b. Elapsed Time (SalesRankQuery): 0.03125
c. Compute Rank: 0.015625
d. ElapsedTime (UpdateRank):0
Call GenSales(1000)
b. Elapsed Time (SalesRankQuery): 3.046875
c. Compute Rank:0.09375
d. ElapsedTime (UpdateRank):0
Call GenSales(2000)
b. Elapsed Time (SalesRankQuery): 12.078125
c. Compute Rank:0.1875
d. ElapsedTime (UpdateRank):0
Call GenSales(3000)
b.Elapsed Time (SalesRankQuery): 27.609375
c.Compute Rank:0.28125
d.ElapsedTime (UpdateRank):0.015625
Call GenSales(4000)
b. Elapsed Time (SalesRankQuery): 47.9375
c. Compute Rank: 0.390625
d. ElapsedTime (UpdateRank):0.015625
From these results, it seems to me that running the code to update the Rank
field would be significantly faster than running the query; even with small
data sets. I know this is heresey, but please, show me the flaw in my
logic.
Dale
Marshall Barton said:
Lotto said:
I have the module below to rank a set of records in a table. My
problem is that it ranks the wrong way, I want the largest number in
the dataset to be number one, instead it looks like the snapshot
below. FYI - the records are sorted before ranking in the order
shown:
Sold To Payer Order No Ref Doc No Amt Pgm
ClRank
50576 50576 0054213 200700348699 $2,603.16 DLS 5
50576 50576 0053441 200700348697 $400.00 MDF 4
50576 50576 0052465 200700348695 $322.00 MDF 3
50576 50576 0052464 200700348694 $280.00 MDF 2
50576 50576 0052118 200700259700 $154.56 DLS 1
Code is below:
Function AddCustRank()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Dim sRep As String
Dim sRepCompare As String
Set db = CurrentDb
Set rs = db.OpenRecordset("select * FROM [Sales no1] order by Brk,
[Sold to], Amt")
rs.MoveFirst
'initialize the first record
sRep = rs.Fields("Sold to")
Do While Not rs.EOF
'initialize the rank count for a new rep
i = 1
sRepCompare = rs.Fields("Sold to")
Do While Not rs.EOF And sRepCompare = sRep
rs.Edit
rs.Fields("ClRank").Value = i
rs.Update
rs.MoveNext
i = i + 1
If Not rs.EOF Then sRep = rs.Fields("Sold to")
Loop
Loop
Speaking generally, that kind of number should not be stored
in the table because the table would need to be updated
every time a record is added, deleted or modified.
Normally, it should be recalculated any time you need to
view it in a form or report. This can be done in a query by
using a subquery:
SELECT S.*,
(SELECT Count(*)
FROM [Sales no1] As X
WHERE X.[Sold to] = S.[Sold to]
And X.Amt <= S.Amt) As Rank
FROM [Sales no1] As S
which can be used as the form/report's record source.
