Solved

MS Access Ranking

Posted on 2014-01-09
6
231 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

713 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