Solved

MS Access Ranking

Posted on 2014-01-09
6
235 Views
Last Modified: 2014-01-09
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.
0
Comment
Question by:Brogrim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39767643
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
 
LVL 58
ID: 39767677
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
 

Author Closing Comment

by:Brogrim
ID: 39767678
Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39767711
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
 
LVL 58
ID: 39767787
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
 
LVL 58
ID: 39767819
One other thing, Floor() is not valid in Access/JET SQL.   You'll need to change that to Int().

Jim.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question