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?
 
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

0
 
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.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Thanks
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Dale FyeCommented:
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

0
 
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.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.