Total

  • Thread starter Thread starter Yousoft
  • Start date Start date
Y

Yousoft

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
 
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
 
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 said:
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
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
Click to expand...
.
Click to expand...
 
Then ask your question in an Excel forum.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
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
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top