total every 20 employees (1 Viewer)

mohamedmatter

Registered User.
Local time
Today, 05:25
Joined
Oct 25, 2015
Messages
112
i want to calculate total every 20 employees without insert row after row 20 or without separate all employees. to print 20 employees in paper

is it possible

I attach file
 

Attachments

  • payrool.xlsx
    10.9 KB · Views: 152

Ranman256

Well-known member
Local time
Today, 08:25
Joined
Apr 9, 2015
Messages
4,337
How do you calculate an employee?
you want any 20?
the 1st 20?
Pick random 20?
 

June7

AWF VIP
Local time
Today, 04:25
Joined
Mar 9, 2014
Messages
5,423
The following will generate page break every 20 records.

Create textbox in Detail section.
Name: tbxCount
ControlSource: =1
RunningSum: OverAll or OverGroup, both worked for me

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.PageBreak1.Visible = Me.tbxCount Mod 20 = 0
End Sub

Format events only trigger for PrintPreview or direct to printer.

Now for the total.

Another textbox in Detail section.
Name: tbxSum
ControlSource: field to sum
RunningSum: same as above

Another textbox in Page footer.
ControlSource: =tbxSum

Total on each page will be cumulative. If you don't want cumulative, this gets really complicated.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:25
Joined
Jan 14, 2017
Messages
18,186
June
Your solution looks good for an Access report
Just one problem. This is in the Excel forum.
Perhaps the OP can confirm what he wants.
 

June7

AWF VIP
Local time
Today, 04:25
Joined
Mar 9, 2014
Messages
5,423
Ooops, did not notice that. Now I understand the 'without insert row'.

So either manually set page breaks and Sum expressions every 20 rows in column F or some fancy VBA code to do that.

Or set a link to the spreadsheet in Access and build report as described.

Or migrate all to Access and eliminate Excel.
 
Last edited:

mohamedmatter

Registered User.
Local time
Today, 05:25
Joined
Oct 25, 2015
Messages
112
The following will generate page break every 20 records.

Create textbox in Detail section.
Name: tbxCount
ControlSource: =1
RunningSum: OverAll or OverGroup, both worked for me

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.PageBreak1.Visible = Me.tbxCount Mod 20 = 0
End Sub

Format events only trigger for PrintPreview or direct to printer.

Now for the total.

Another textbox in Detail section.
Name: tbxSum
ControlSource: field to sum
RunningSum: same as above

Another textbox in Page footer.
ControlSource: =tbxSum

Total on each page will be cumulative. If you don't want cumulative, this gets really complicated.
I would like to have an example of the attached file. Please be thankful
 

June7

AWF VIP
Local time
Today, 04:25
Joined
Mar 9, 2014
Messages
5,423
Why don't you follow the guidelines and build report?

The design I describe is dependent on the ID being sequential with no gaps.
 

Users who are viewing this thread

Top Bottom