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
Solved

MS Access Ranking

Posted on 2014-01-09
6
230 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
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 57
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 57
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 57
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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