Display every nth record in report

[liz malcolm]

I am trying to create a report showing 3 sets of case numbers (pre-
defined, 1000 - 1371) per page. Some of the case numbers might not be
assigned (data entry errors). The first set starts with the 1st case
number. The 2nd set should start with the 24th case number. The 3rd
set should start with the 47th. The report is based on a query that
shows only assigned numbers.

The report should look like this:
Pages 1-23
1000 - 1023 for the 1st set
1024 - 1047 for the 2nd set
1048 - 1071 for the 3rd set

in other words
Page 1 Page 2
1000 1001
1024 1025
1048 1049

I tried int_CaseNbr + 23 etc, but that doesn't take into account the
missing case numbers. Is this even possible?

Thanks in advance, Liz

 

[KARL DEWEY]

I can think of 2 ways.
1- Left join a table of case numbers so that all numbers are listed and use
your int_CaseNbr + 23.

2- Add an autonumber and use ([Autonumber]\23) +1 to give you page number to
group on.

 

[KenSheridan via AccessMonster.com]

Liz:

Jo Celko has published two methods for retuning every nth row:

SELECT CaseNumber
FROM Cases AS C1
WHERE EXISTS
(SELECT MAX(CaseNumber)
FROM Cases AS C2
WHERE C1.CaseNumber >= C2.CaseNumber
HAVING COUNT(*) MOD 24 = 0);

or:

SELECT C1.CaseNumber
FROM Cases AS C1, Cases AS C2
WHERE C1.CaseNumber >= C2.CaseNumber
GROUP BY C1.CaseNumber
HAVING COUNT(*) MOD 24 = 0;

These will return the nth row ( i.e. the last) per subset. Amending them as
follows should return the first per subset:

SELECT CaseNumber
FROM Cases AS C1
WHERE EXISTS
(SELECT MAX(CaseNumber)
FROM Cases AS C2
WHERE C1.CaseNumber >= C2.CaseNumber
HAVING COUNT(*) MOD 24 = 1);

or:

SELECT C1.CaseNumber
FROM Cases AS C1, Cases AS C2
WHERE C1.CaseNumber >= C2.CaseNumber
GROUP BY C1.CaseNumber
HAVING COUNT(*) MOD 24 = 1;

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Apologies to Joe. I seem to have changed his sex. He's Joe Celko, not Jo.

If you'd seen him you'd never make take that mistake. I can assure you!

Ken Sheridan
Stafford, England

 

[liz malcolm]

Liz:

Jo Celko has published two methods for retuning every nth row:

SELECT CaseNumber
  FROM Cases AS C1
 WHERE EXISTS
    (SELECT MAX(CaseNumber)
     FROM Cases AS C2
     WHERE C1.CaseNumber >= C2.CaseNumber
     HAVING COUNT(*) MOD 24 = 0);

or:

SELECT C1.CaseNumber
  FROM Cases AS C1, Cases AS C2
 WHERE C1.CaseNumber >= C2.CaseNumber
 GROUP BY C1.CaseNumber
HAVING COUNT(*) MOD 24 = 0;

These will return the nth row ( i.e. the last) per subset.  Amending them as
follows should return the first per subset:

SELECT CaseNumber
  FROM Cases AS C1
 WHERE EXISTS
    (SELECT MAX(CaseNumber)
     FROM Cases AS C2
     WHERE C1.CaseNumber >= C2.CaseNumber
     HAVING COUNT(*) MOD 24 = 1);

or:

SELECT C1.CaseNumber
  FROM Cases AS C1, Cases AS C2
 WHERE C1.CaseNumber >= C2.CaseNumber
 GROUP BY C1.CaseNumber
HAVING COUNT(*) MOD 24 = 1;

Ken Sheridan
Stafford, England

Thanks Ken

I'll try your suggestion. Liz