MS Access Ranking

I have a table with 40,000 records each with a unique ID (number)

I want to create a report that will group the records in bathes of 100. I could run a query for each report but that will be 400 queries.

I don't know if I should attempt this in the query or in the grouping part of the report.
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do it using dcount , I think (untested):
 select 1 + floor( DCount("[ID]","[mytable]","[ID]<" & [ID]) / 400 ) AS batch_number
  , id
  from yourtable
order by id 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
SELECT Table1_A.ID, (Select Count(*) From Table1 Where [Table1].[ID]<[Table1_A].[ID]) AS RecNum, Fix([RecNum]/100)+1 AS [GroupNum]
FROM Table1 AS Table1_A;

Then group on GroupNum within your report.

Jim.
BrogrimInformation Systems Development ManagerAuthor Commented:
Thanks
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dale FyeOwner, Dev-Soln LLCCommented:
My first question would be why?

After that, I'd ask how do you want it to "batch"?  Are you going to run the report separately for each batch of 100 records, or do you just want to begin a new group and push the first set of the next records to the top of the next page?

If you simply want to run the report with 100 records at a time.  If that is the case, I would probably create a recordset that contains the minimum and maximum [ID] values for each 100 record range.  Then loop through that recordset, opening the report with a WHERE clause that puts the ID between those two values.  To get that recordset  I would do something like the following:
SELECT ([Rank]-1)\100 AS Expr1
      , Min(RecRank.ID) AS MinID
      , Max(RecRank.ID) AS MaxID
FROM (
SELECT T1.ID, Count(T2.ID) AS Rank
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
)  AS RecRank
GROUP BY ([Rank]-1)\100;

Open in new window

Save that as qry_ReportBlocks.

The subquery within this query will determine the rank of each ID by counting the number of records with an ID value that is less than or equal to each ID.  The outer part of the query will define your groups of 100 and will identify the minimum and maximum ID values within each of those blocks.

Once you have that recordset, it is simply a matter of opening the report and changing the filter value of the report, something like:
Dim rs as DAO.Recordset

set rs = currentdb.querydefs("qry_ReportBlocks").openrecordset dbfailonerror

While not rs.eof
    docmd.OpenReport "reportName", , , "[ID] >= " & rs!MinID & " AND [ID] <= " & rs!MaxID
    rs.movenext
Wend
rs.close
set rs = nothing

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
A note on the accepted solution:

  You don't want to be using domain functions in a query.  They are un-optimizeable by the query parser and will yield poor performance.

  All the domain functions represent SQL statements, which in a query can be written directly.

 The domain functions exist in Access for use in places where a SQL statement is not allowed, such as the controlsource of a control.  Even then, they should be used sparingly as they carry quite a bit of overhead and depending on the amount of data to be returned, there are often better ways to get it.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
One other thing, Floor() is not valid in Access/JET SQL.   You'll need to change that to Int().

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.