Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-03-2019, 11:25 AM   #1
mohamedmatter
Newly Registered User
 
Join Date: Oct 2015
Posts: 81
Thanks: 21
Thanked 0 Times in 0 Posts
mohamedmatter is on a distinguished road
total every 20 employees

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
Attached Files
File Type: xlsx payrool.xlsx (10.9 KB, 13 views)

mohamedmatter is offline   Reply With Quote
Old 04-03-2019, 01:01 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,319
Thanks: 0
Thanked 728 Times in 713 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: total every 20 employees

How do you calculate an employee?
you want any 20?
the 1st 20?
Pick random 20?
Ranman256 is offline   Reply With Quote
Old 04-03-2019, 08:39 PM   #3
mohamedmatter
Newly Registered User
 
Join Date: Oct 2015
Posts: 81
Thanks: 21
Thanked 0 Times in 0 Posts
mohamedmatter is on a distinguished road
Re: total every 20 employees

Quote:
Originally Posted by Ranman256 View Post
How do you calculate an employee?
you want any 20?
the 1st 20?
Pick random 20?
from id 1-20 in paper and 21-40 in paper -etc

mohamedmatter is offline   Reply With Quote
Old 04-03-2019, 08:57 PM   #4
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,797
Thanks: 0
Thanked 433 Times in 429 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: total every 20 employees

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 04-03-2019 at 09:33 PM.
June7 is offline   Reply With Quote
Old 04-03-2019, 10:52 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,255
Thanks: 105
Thanked 2,487 Times in 2,283 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: total every 20 employees

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-03-2019, 11:51 PM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,797
Thanks: 0
Thanked 433 Times in 429 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: total every 20 employees

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 04-04-2019 at 12:15 AM.
June7 is offline   Reply With Quote
Old 04-04-2019, 10:09 PM   #7
mohamedmatter
Newly Registered User
 
Join Date: Oct 2015
Posts: 81
Thanks: 21
Thanked 0 Times in 0 Posts
mohamedmatter is on a distinguished road
Re: total every 20 employees

Quote:
Originally Posted by June7 View Post
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

mohamedmatter is offline   Reply With Quote
Old 04-04-2019, 10:35 PM   #8
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,797
Thanks: 0
Thanked 433 Times in 429 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: total every 20 employees

Why don't you follow the guidelines and build report?

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

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mkdir for all Employees ECEK Modules & VBA 5 10-26-2017 01:27 AM
Notice to All Employees Joe8915 Politics & Current Events 38 10-28-2008 10:24 AM
Employees Bellla Tables 1 05-08-2007 12:57 AM
Calculate total hours (for all employees) on each department? KevinSlater Queries 3 09-29-2005 01:49 AM
Active Employees/Inactive Employees desireemm General 2 01-14-2004 11:49 AM




All times are GMT -8. The time now is 02:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World