Display every nth record in report

liz malcolm · External Since: Oct 02, 2009 Posts: 2

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 · External Since: Mar 03, 2006 Posts: 1616

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.

--
Build a little, test a little.

"liz malcolm" wrote:

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

KenSheridan via AccessMon · External Since: May 16, 2009 Posts: 68

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

liz malcolm wrote:
>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

--
Message posted via http://www.accessmonster.com

liz malcolm · External Since: Oct 02, 2009 Posts: 2

On Oct 4, 12:39 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> 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
>
>
>
>
>
> liz malcolm wrote:
> >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
>
> --
> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>
> - Show quoted text -

Thanks Ken

I'll try your suggestion. Liz