New page after 15 records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KHogwood-Thompson - 28 Feb 2008 14:47 GMT

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??

Signature

K Hogwood-Thompson

Reply to this Message

Duane Hookom - 28 Feb 2008 18:06 GMT

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.

Signature

Duane Hookom
Microsoft Access MVP

> Hi All,
>
> I have a report that lists payments to be made through the BACS system
> direct with our bank. When I print the report, I need it to only have a
> maximum of 15 records on each page with subtotals after each batch of 15
> records. How can I achieve this easily??

Reply to this Message

KHogwood-Thompson - 06 Mar 2008 16:50 GMT

Thanks for the information, I have created a query to test the sql and the
query is as follows:

SELECT Val(DCount("*","Payroll Employees Pay Slips"," EMPLOYEE REF<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

where the underlying table is called Payroll Employees Pay Slips, and the
field that I want to limit the records to 15 is called EMPLOYEE REF

I get a syntax error when I run the query and the GroupNum column displays
the #Error code. Is there something that I am missing here?

Signature

K Hogwood-Thompson

> It's a bit difficult to do this without understanding your table structure. I
> would create a column in your report's record source query that returns a
[quoted text clipped - 15 lines]
> > maximum of 15 records on each page with subtotals after each batch of 15
> > records. How can I achieve this easily??

Reply to this Message

Duane Hookom - 06 Mar 2008 21:13 GMT

Your issue is probably caused by spaces in your field and table names. If
[EMPLOYEE REF] is numeric, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

If it is text, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<""" &
[EMPLOYEE REF] & """"))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

Signature

Duane Hookom
Microsoft Access MVP

> Thanks for the information, I have created a query to test the sql and the
> query is as follows:
[quoted text clipped - 29 lines]
> > > maximum of 15 records on each page with subtotals after each batch of 15
> > > records. How can I achieve this easily??

Reply to this Message

KHogwood-Thompson - 07 Mar 2008 08:25 GMT

Thanks for that Duane, not sure if that was the problem or not, as I get the
following error when I run that SQL:

"The expression that you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'CO?' "

The underlying query "Payroll Employees Pay Slips" contains two Integer
parameters, [CO?] and [WEEK NO?], basically selecting the company number and
the payroll week.

Signature

K Hogwood-Thompson

> Your issue is probably caused by spaces in your field and table names. If
> [EMPLOYEE REF] is numeric, try:
[quoted text clipped - 42 lines]
> > > > maximum of 15 records on each page with subtotals after each batch of 15
> > > > records. How can I achieve this easily??

Reply to this Message

Duane Hookom - 07 Mar 2008 16:01 GMT

IMHO, don't ever use parameter prompts in queries. Take a look at
http://www.tek-tips.com/faqs.cfm?fid=6763 and replace your criteria with
references to controls on a form.

Signature

Duane Hookom
Microsoft Access MVP

> Thanks for that Duane, not sure if that was the problem or not, as I get the
> following error when I run that SQL:
[quoted text clipped - 52 lines]
> > > > > maximum of 15 records on each page with subtotals after each batch of 15
> > > > > records. How can I achieve this easily??

Reply to this Message