New Page

[Azuquita]

This is probably a very simple thing to do, but I am having a hard time
finding the answer.

I have a database with over 300 records. I have created a report that
should only generate 16 records at a time. When I print it, the first page
prints records 1-16, which is perfect, but the second page continues with
record 17.

How can I make sure each report only contains 16 records at a time?

Thank you

 

[Steve]

Base your report on a query and set an appropriate criteria so the query
only returns 16 records.

Steve
(e-mail address removed)

 

[John Spencer]

I'm not sure I understand.

Do you mean that the first page of the report has 16 records and that
subsequent pages have 17 records? Or are you trying to print separate reports
that have 16 records each? Or are you trying to do something else?

Please expand a bit on what you are trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Azuquita]

Thank you for your response.

Yes, I am trying to print separate reports that have 16 records each.

 

[Azuquita]

Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

 

[Azuquita]

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the value
entered at the parameter prompt:

SELECT TOP 16 *
FROM MyTable As T1
WHERE MyID <=
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE EXISTS
(SELECT MAX(MyID)
FROM MyTable AS T3
WHERE T2.MyID >= T3.MyID
HAVING COUNT(*) MOD (16 *[Enter group number:]) = 0))
ORDER BY MyID DESC;

where MyTable is the name of a table (or query) and MyID is a column of
unique values by which you want to order each set of 16 rows returned. If 1
is entered at the parameter prompt the first set of 16 rows in the MyID sort
order will be returned, if 2 is entered the second set of 16 , if 3 the third
set of 16 and so on.

So you can open the report repeatedly and answer 1, then 2 and so on until no
more rows are returned, in which case you can use the report's NoData event
procedure to pop up a message box and cancel the printing by setting the
return value of the event's Cancel argument to True. Or you could automate
the printing of the successive reports with some VBA code using a loop which
terminates on the error occurring when the OpenReport method is called but
the opening of the report is then cancelled by the code in its NoData event
procedure.

One thing to note is that the query returns each set in descending order.
This is necessary for the query to work, but doesn't affect the order in
which the rows can be returned in the report as you would set its sort order
by means of its internal sorting and grouping mechanism.

I've tested the query against a table of 1000 rows, and its performance,
while not lightning fast, should be acceptable in the context of printing
reports.

Ken Sheridan
Stafford, England

Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

I'm not sure I understand.

[quoted text clipped - 29 lines]

.

 

[Steve]

Azuquita,

His solution is ridiciulous! You asked for a simple solution and he gave you
an extremely complex solution. Post the tables you are reporting from and we
will give you a simple solution.

Steve
(e-mail address removed)

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the
value
entered at the parameter prompt:

SELECT TOP 16 *
FROM MyTable As T1
WHERE MyID <=
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE EXISTS
(SELECT MAX(MyID)
FROM MyTable AS T3
WHERE T2.MyID >= T3.MyID
HAVING COUNT(*) MOD (16 *[Enter group number:]) = 0))
ORDER BY MyID DESC;

where MyTable is the name of a table (or query) and MyID is a column of
unique values by which you want to order each set of 16 rows returned.
If 1
is entered at the parameter prompt the first set of 16 rows in the MyID
sort
order will be returned, if 2 is entered the second set of 16 , if 3 the
third
set of 16 and so on.

So you can open the report repeatedly and answer 1, then 2 and so on
until no
more rows are returned, in which case you can use the report's NoData
event
procedure to pop up a message box and cancel the printing by setting the
return value of the event's Cancel argument to True. Or you could
automate
the printing of the successive reports with some VBA code using a loop
which
terminates on the error occurring when the OpenReport method is called
but
the opening of the report is then cancelled by the code in its NoData
event
procedure.

One thing to note is that the query returns each set in descending order.
This is necessary for the query to work, but doesn't affect the order in
which the rows can be returned in the report as you would set its sort
order
by means of its internal sorting and grouping mechanism.

I've tested the query against a table of 1000 rows, and its performance,
while not lightning fast, should be acceptable in the context of printing
reports.

Ken Sheridan
Stafford, England

Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

I'm not sure I understand.

[quoted text clipped - 29 lines]

.

 

[Gina Whipp]

Azuquita,

You would use this as a query. What is the name of your table or query and
the name of the Primary Key that has unique values in the before mentioned
table or query.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the
value
entered at the parameter prompt:

SELECT TOP 16 *
FROM MyTable As T1
WHERE MyID <=
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE EXISTS
(SELECT MAX(MyID)
FROM MyTable AS T3
WHERE T2.MyID >= T3.MyID
HAVING COUNT(*) MOD (16 *[Enter group number:]) = 0))
ORDER BY MyID DESC;

where MyTable is the name of a table (or query) and MyID is a column of
unique values by which you want to order each set of 16 rows returned.
If 1
is entered at the parameter prompt the first set of 16 rows in the MyID
sort
order will be returned, if 2 is entered the second set of 16 , if 3 the
third
set of 16 and so on.

So you can open the report repeatedly and answer 1, then 2 and so on
until no
more rows are returned, in which case you can use the report's NoData
event
procedure to pop up a message box and cancel the printing by setting the
return value of the event's Cancel argument to True. Or you could
automate
the printing of the successive reports with some VBA code using a loop
which
terminates on the error occurring when the OpenReport method is called
but
the opening of the report is then cancelled by the code in its NoData
event
procedure.

One thing to note is that the query returns each set in descending order.
This is necessary for the query to work, but doesn't affect the order in
which the rows can be returned in the report as you would set its sort
order
by means of its internal sorting and grouping mechanism.

I've tested the query against a table of 1000 rows, and its performance,
while not lightning fast, should be acceptable in the context of printing
reports.

Ken Sheridan
Stafford, England

Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

I'm not sure I understand.

[quoted text clipped - 29 lines]

.

 

[Azuquita]

My day is over. I will try to explain tomorrow. Please come back tomorrow.


Thank you.

Azuquita,

His solution is ridiciulous! You asked for a simple solution and he gave you
an extremely complex solution. Post the tables you are reporting from and we
will give you a simple solution.

Steve
(e-mail address removed)

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the
value
entered at the parameter prompt:

SELECT TOP 16 *
FROM MyTable As T1
WHERE MyID <=
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE EXISTS
(SELECT MAX(MyID)
FROM MyTable AS T3
WHERE T2.MyID >= T3.MyID
HAVING COUNT(*) MOD (16 *[Enter group number:]) = 0))
ORDER BY MyID DESC;

where MyTable is the name of a table (or query) and MyID is a column of
unique values by which you want to order each set of 16 rows returned.
If 1
is entered at the parameter prompt the first set of 16 rows in the MyID
sort
order will be returned, if 2 is entered the second set of 16 , if 3 the
third
set of 16 and so on.

So you can open the report repeatedly and answer 1, then 2 and so on
until no
more rows are returned, in which case you can use the report's NoData
event
procedure to pop up a message box and cancel the printing by setting the
return value of the event's Cancel argument to True. Or you could
automate
the printing of the successive reports with some VBA code using a loop
which
terminates on the error occurring when the OpenReport method is called
but
the opening of the report is then cancelled by the code in its NoData
event
procedure.

One thing to note is that the query returns each set in descending order.
This is necessary for the query to work, but doesn't affect the order in
which the rows can be returned in the report as you would set its sort
order
by means of its internal sorting and grouping mechanism.

I've tested the query against a table of 1000 rows, and its performance,
while not lightning fast, should be acceptable in the context of printing
reports.

Ken Sheridan
Stafford, England

Azuquita wrote:
Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

I'm not sure I understand.

[quoted text clipped - 29 lines]

.

.

 

[Azuquita]

My Query name is: "CSD" and the column name is "Record Number"

Azuquita,

You would use this as a query. What is the name of your table or query and
the name of the Primary Key that has unique values in the before mentioned
table or query.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the
value
entered at the parameter prompt:

SELECT TOP 16 *
FROM MyTable As T1
WHERE MyID <=
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE EXISTS
(SELECT MAX(MyID)
FROM MyTable AS T3
WHERE T2.MyID >= T3.MyID
HAVING COUNT(*) MOD (16 *[Enter group number:]) = 0))
ORDER BY MyID DESC;

where MyTable is the name of a table (or query) and MyID is a column of
unique values by which you want to order each set of 16 rows returned.
If 1
is entered at the parameter prompt the first set of 16 rows in the MyID
sort
order will be returned, if 2 is entered the second set of 16 , if 3 the
third
set of 16 and so on.

So you can open the report repeatedly and answer 1, then 2 and so on
until no
more rows are returned, in which case you can use the report's NoData
event
procedure to pop up a message box and cancel the printing by setting the
return value of the event's Cancel argument to True. Or you could
automate
the printing of the successive reports with some VBA code using a loop
which
terminates on the error occurring when the OpenReport method is called
but
the opening of the report is then cancelled by the code in its NoData
event
procedure.

One thing to note is that the query returns each set in descending order.
This is necessary for the query to work, but doesn't affect the order in
which the rows can be returned in the report as you would set its sort
order
by means of its internal sorting and grouping mechanism.

I've tested the query against a table of 1000 rows, and its performance,
while not lightning fast, should be acceptable in the context of printing
reports.

Ken Sheridan
Stafford, England

Azuquita wrote:
Thank you for your prompt response.

Yes, I am trying to print separate reports that have 16 records each.

I'm not sure I understand.

[quoted text clipped - 29 lines]

.

.

 

[Azuquita]

Okay, I did what you asked, but I get the following error: Circular
reference caused by 'CSD'. FYI, my Query name is CSD.

Open the query designer and switch to SQL view. Paste in the SQL statement
into as I posted it, and then change each instance of MyTable to the name of
the table or query on which your report is based. Change each instance of
MyID to the name of a column in your table or query whose values are unique
and which will determine the order by which each block of 16 rows is returned,
e.g. if it’s a date column then the first block of 16 rows would be the
earliest 16 dates, the next would be the next earliest 16 dates and so on.
The values in the column must be unique, however.

Open the query to test it before amending the report. If you are satisfied
that it is returning the correct 16 rows for whatever value you enter at the
parameter prompt you can then set the report's RecordSource property to the
name of the query.

Within each block of 16 you can sort the rows returned in the report however
you wish using the report's sorting and grouping mechanism.

Ken Sheridan
Stafford, England

Wow! Way more than I know what to do, but, where do I enter this info?

The following query will return a group of 16 rows on the basis of the value
entered at the parameter prompt:

[quoted text clipped - 47 lines]

.

--



.