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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|