A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Total



 
 
Thread Tools Display Modes
  #1  
Old April 13th, 2010, 11:10 AM posted to microsoft.public.access.gettingstarted
Yousoft
external usenet poster
 
Posts: 35
Default Total

Dear All
Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID
& date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc.
Thanks

Remarks my data not include any empty raw

Working data as below
Date Emp #.ID No. of Hours
01/03/10 116 4.00
01/03/10 116 2.00
01/03/10 116 5.00
01/03/10 116 5.00
03/03/10 116 11.00
04/03/10 116 11.00
28/03/10 116 11.00
01/03/10 120 2.00
01/03/10 120 5.00
01/03/10 120 4.00
02/03/10 120 9.00
02/03/10 120 2.00
03/03/10 120 11.00
04/03/10 120 11.00
23/03/10 120 11.00
25/03/10 120 6.00
25/03/10 120 5.00
01/03/10 131 11.00
02/03/10 131 11.00
03/03/10 131 6.00
03/03/10 131 5.00
16/03/10 131 11.00
25/03/10 131 2.00
25/03/10 131 9.00
04/03/10 150 5.00
04/03/10 150 2.00
04/03/10 150 4.00
14/03/10 150 9.00
15/03/10 150 2.00
15/03/10 150 9.00
16/03/10 150 2.00
16/03/10 150 9.00
17/03/10 150 9.00
17/03/10 150 2.00

  #2  
Old April 13th, 2010, 01:37 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Total

A query would be
SELECT [Date], [Emp #.Id], Sum([No. of Hours]) as Hours
FROM YourDataTable
GROUP BY [Date], [Emp #.Id]

You can build that query in query design view.
== Add your table to a new query
== Add the three fields
== SELECT View: Totals from the menu
== Change GROUP BY to SUM under the No. of Hours field


You could use the DSUM function if you wanted to specify a specific date and
employee. Whether you use DSUM or a query or another expression does depend
on WHERE you need to do this. In a report or a form?

If in a report, are you grouping the records by Employee and Date with a group
footer? If so the simplest way to sub-total would be to use a control in the
group footer with its source an expression like
=Sum([No. of Hours])

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

Yousoft wrote:
Dear All
Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID
& date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc.
Thanks

Remarks my data not include any empty raw

Working data as below
Date Emp #.ID No. of Hours
01/03/10 116 4.00
01/03/10 116 2.00
01/03/10 116 5.00
01/03/10 116 5.00
03/03/10 116 11.00
04/03/10 116 11.00
28/03/10 116 11.00
01/03/10 120 2.00
01/03/10 120 5.00
01/03/10 120 4.00
02/03/10 120 9.00
02/03/10 120 2.00
03/03/10 120 11.00
04/03/10 120 11.00
23/03/10 120 11.00
25/03/10 120 6.00
25/03/10 120 5.00
01/03/10 131 11.00
02/03/10 131 11.00
03/03/10 131 6.00
03/03/10 131 5.00
16/03/10 131 11.00
25/03/10 131 2.00
25/03/10 131 9.00
04/03/10 150 5.00
04/03/10 150 2.00
04/03/10 150 4.00
14/03/10 150 9.00
15/03/10 150 2.00
15/03/10 150 9.00
16/03/10 150 2.00
16/03/10 150 9.00
17/03/10 150 9.00
17/03/10 150 2.00

  #3  
Old April 14th, 2010, 08:11 AM posted to microsoft.public.access.gettingstarted
Yousoft
external usenet poster
 
Posts: 35
Default Total

Thank you for your respond but I'm sorry I miss placed my question, should be
in Excel section. so I, need Excel formula or VB macro.

"John Spencer" wrote:

A query would be
SELECT [Date], [Emp #.Id], Sum([No. of Hours]) as Hours
FROM YourDataTable
GROUP BY [Date], [Emp #.Id]

You can build that query in query design view.
== Add your table to a new query
== Add the three fields
== SELECT View: Totals from the menu
== Change GROUP BY to SUM under the No. of Hours field


You could use the DSUM function if you wanted to specify a specific date and
employee. Whether you use DSUM or a query or another expression does depend
on WHERE you need to do this. In a report or a form?

If in a report, are you grouping the records by Employee and Date with a group
footer? If so the simplest way to sub-total would be to use a control in the
group footer with its source an expression like
=Sum([No. of Hours])

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

Yousoft wrote:
Dear All
Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID
& date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc.
Thanks

Remarks my data not include any empty raw

Working data as below
Date Emp #.ID No. of Hours
01/03/10 116 4.00
01/03/10 116 2.00
01/03/10 116 5.00
01/03/10 116 5.00
03/03/10 116 11.00
04/03/10 116 11.00
28/03/10 116 11.00
01/03/10 120 2.00
01/03/10 120 5.00
01/03/10 120 4.00
02/03/10 120 9.00
02/03/10 120 2.00
03/03/10 120 11.00
04/03/10 120 11.00
23/03/10 120 11.00
25/03/10 120 6.00
25/03/10 120 5.00
01/03/10 131 11.00
02/03/10 131 11.00
03/03/10 131 6.00
03/03/10 131 5.00
16/03/10 131 11.00
25/03/10 131 2.00
25/03/10 131 9.00
04/03/10 150 5.00
04/03/10 150 2.00
04/03/10 150 4.00
14/03/10 150 9.00
15/03/10 150 2.00
15/03/10 150 9.00
16/03/10 150 2.00
16/03/10 150 9.00
17/03/10 150 9.00
17/03/10 150 2.00

.

  #4  
Old April 15th, 2010, 01:23 AM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Total

Then ask your question in an Excel forum.

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

Yousoft wrote:
Thank you for your respond but I'm sorry I miss placed my question, should be
in Excel section. so I, need Excel formula or VB macro.

"John Spencer" wrote:

A query would be
SELECT [Date], [Emp #.Id], Sum([No. of Hours]) as Hours
FROM YourDataTable
GROUP BY [Date], [Emp #.Id]

You can build that query in query design view.
== Add your table to a new query
== Add the three fields
== SELECT View: Totals from the menu
== Change GROUP BY to SUM under the No. of Hours field


You could use the DSUM function if you wanted to specify a specific date and
employee. Whether you use DSUM or a query or another expression does depend
on WHERE you need to do this. In a report or a form?

If in a report, are you grouping the records by Employee and Date with a group
footer? If so the simplest way to sub-total would be to use a control in the
group footer with its source an expression like
=Sum([No. of Hours])

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

Yousoft wrote:
Dear All
Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID
& date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc.
Thanks

Remarks my data not include any empty raw

Working data as below
Date Emp #.ID No. of Hours
01/03/10 116 4.00
01/03/10 116 2.00
01/03/10 116 5.00
01/03/10 116 5.00
03/03/10 116 11.00
04/03/10 116 11.00
28/03/10 116 11.00
01/03/10 120 2.00
01/03/10 120 5.00
01/03/10 120 4.00
02/03/10 120 9.00
02/03/10 120 2.00
03/03/10 120 11.00
04/03/10 120 11.00
23/03/10 120 11.00
25/03/10 120 6.00
25/03/10 120 5.00
01/03/10 131 11.00
02/03/10 131 11.00
03/03/10 131 6.00
03/03/10 131 5.00
16/03/10 131 11.00
25/03/10 131 2.00
25/03/10 131 9.00
04/03/10 150 5.00
04/03/10 150 2.00
04/03/10 150 4.00
14/03/10 150 9.00
15/03/10 150 2.00
15/03/10 150 9.00
16/03/10 150 2.00
16/03/10 150 9.00
17/03/10 150 9.00
17/03/10 150 2.00

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.