dsum

[babs]

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

:

 

[KARL DEWEY]

DSum totals for the whole domain without additional criteria.
Try this --
SELECT babs.Worker, babs.[Job #], babs.Workdate, babs.[Vacation Time],
(SELECT Sum(XX.[Vacation Time]) FROM babs AS [XX] WHERE [XX].[Workdate] <=
[babs].[Workdate]) AS SumOfHRS
FROM babs;

 

[John W. Vinson]

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

"Frank H" wrote:

Do you have a question, Frank? I couldn't parse one out of the above.

Note also that if you have a "total vacation taken" stored in your Table
you're making a mistake: that should be dynamically calculated on the fly, not
stored anywhere. Is this the structure of your actual table, or the desired
output?

 

[babs]

Thanks so much for helping - not sure where to put the code you suggested -
here is what the text box for the Dsum in the subform footer is = to.

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]","[Man Name]= """ & [Man
name] & """" & " AND [workdate] Between
#1/1/09# AND #12/31/09#")

See previous post for issue - not sure where to use your suggestion???
thanks for helping,
Barb

DSum totals for the whole domain without additional criteria.
Try this --
SELECT babs.Worker, babs.[Job #], babs.Workdate, babs.[Vacation Time],
(SELECT Sum(XX.[Vacation Time]) FROM babs AS [XX] WHERE [XX].[Workdate] <=
[babs].[Workdate]) AS SumOfHRS
FROM babs;

--
Build a little, test a little.

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

:

 

[KARL DEWEY]

not sure where to use your suggestion?
What I post was the SQL for a query. I update table and field names.
Create a query in design view, select no table, click on VIEW - SQL View,
and paste in the window.

SELECT [JeffTime Card MD Query].Worker, [JeffTime Card MD Query].[Job #],
[JeffTime Card MD Query].Workdate, [JeffTime Card MD Query].[HoursVacaCor],
(SELECT Sum(XX.[HoursVacaCor]) FROM [JeffTime Card MD Query] AS [XX] WHERE
[XX].[Workdate] <= [JeffTime Card MD Query].[Workdate]) AS SumOfHRS
FROM [JeffTime Card MD Query]
WHERE [JeffTime Card MD Query].[workdate] Between #1/1/09# AND #12/31/09#;


--
Build a little, test a little.

Thanks so much for helping - not sure where to put the code you suggested -
here is what the text box for the Dsum in the subform footer is = to.

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]","[Man Name]= """ & [Man
name] & """" & " AND [workdate] Between
#1/1/09# AND #12/31/09#")

See previous post for issue - not sure where to use your suggestion???
thanks for helping,
Barb

DSum totals for the whole domain without additional criteria.
Try this --
SELECT babs.Worker, babs.[Job #], babs.Workdate, babs.[Vacation Time],
(SELECT Sum(XX.[Vacation Time]) FROM babs AS [XX] WHERE [XX].[Workdate] <=
[babs].[Workdate]) AS SumOfHRS
FROM babs;

--
Build a little, test a little.

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

:

 

[babs]

I entered the SQL into a new query but it takes a REALLy long time to RUN and
I get
the following result set

worker Job #Workdate HoursVacaCor SumOfVacHRS Vacation Time (hours)

BUSSE, MICHAEL 90600028 03-Aug-09 0 4779.59 136
BUSSE, MICHAEL 90180026 03-Aug-09 0 4779.59 136
BUSSE, MICHAEL 99999 03-Aug-09 8 4779.59 136
BUSSE, MICHAEL 90180048 27-Jul-09 0 4755.59 136
BUSSE, MICHAEL 90170029 27-Jul-09 0 4755.59 136
BUSSE, MICHAEL 90600028 27-Jul-09 0 4755.59 136
BUSSE, MICHAEL 90300031 27-Jul-09 0 4755.59 136
BUSSE, MICHAEL 90170028 20-Jul-09 0 4755.59 136
BUSSE, MICHAEL 90170030 20-Jul-09 0 4755.59 136
BUSSE, MICHAEL 70200130 01-Jun-09 0 4480.59 136
BUSSE, MICHAEL 90200031 01-Jun-09 0 4480.59 136

I just added the vacation time hours that they get for the year at the
end(not using it yet) but the data in sumofVacHRS doesn't make sense - that
# is really high for all people - just want it to total vacation taken year
to date from jan 1 to that date. Later I will subtract what was taken from
what they get for the year - this case 136

not sure why sumofVacHRS is soo high for everyone - should be 0 until
vacation taken??
thanks for helping,
Barb

What I post was the SQL for a query. I update table and field names.
Create a query in design view, select no table, click on VIEW - SQL View,
and paste in the window.

SELECT [JeffTime Card MD Query].Worker, [JeffTime Card MD Query].[Job #],
[JeffTime Card MD Query].Workdate, [JeffTime Card MD Query].[HoursVacaCor],
(SELECT Sum(XX.[HoursVacaCor]) FROM [JeffTime Card MD Query] AS [XX] WHERE
[XX].[Workdate] <= [JeffTime Card MD Query].[Workdate]) AS SumOfHRS
FROM [JeffTime Card MD Query]
WHERE [JeffTime Card MD Query].[workdate] Between #1/1/09# AND #12/31/09#;


--
Build a little, test a little.

Thanks so much for helping - not sure where to put the code you suggested -
here is what the text box for the Dsum in the subform footer is = to.

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]","[Man Name]= """ & [Man
name] & """" & " AND [workdate] Between
#1/1/09# AND #12/31/09#")

See previous post for issue - not sure where to use your suggestion???
thanks for helping,
Barb

DSum totals for the whole domain without additional criteria.
Try this --
SELECT babs.Worker, babs.[Job #], babs.Workdate, babs.[Vacation Time],
(SELECT Sum(XX.[Vacation Time]) FROM babs AS [XX] WHERE [XX].[Workdate] <=
[babs].[Workdate]) AS SumOfHRS
FROM babs;

--
Build a little, test a little.


:

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

:

 

[babs]

For all vacation time taken within the year by each field worker:

Would like all vacation time taken To date within this current year for each
field worker???? not sure which of the first two or a combination of them to
use - I used the 1st one and get way too many hours of vacation taken

yes- my previous code - man name same as field worker(my code i am using man
name) - know should use ss# but not the case - way too deep in to change for
now.

THANKS SOO MUCH FOR HELPING
barb

Barb:

You need to sum the times up to and including the current date:

For all vacation time taken to date by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Workdate] <= #" & Format([Workdate]
,"yyyy-mm-dd") & "#")

For all vacation time taken within the year by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate])

For all vacation time taken on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And [Job #]
= " & [Job #])

For all vacation time taken within the year on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate] & " And [Job #] = " & [Job #])

Formatting the date in the ISO standard date notation format of YYYY-MM-DD
ensures that it will work on any system regardless of the local regional
setting for the date format. Substitute the real table name for YourTable.

In each case the expression is entered as a single line (they'll have been
split over several lines here by your newsreader) as the control's
ControlSource property of a text box in a form or report. In a query the
expressions can be used to return a computed column, omitting the leading =
sign in this case.

Rather than storing the field worker's name in the table its better to store
a numeric FieldWorkerID which references the unique FieldWorkerID primary key
of a separate FieldWorkers table with one row per field worker. This caters
for the possibility of two having the same names (it happens more often than
you'd think). It would also be better to have separate FirstName and
LastName fields in the FieldWorkers table. On a form you can use a combo box
for the FieldWorkerID, set up as follows so that it shows the name:

ControlSource: FieldWorkerID

RowSource: SELECT FieldWorkerID, FirstName & " " & LastName FROM
FieldWorkers ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

"Frank H" wrote:

--
Message posted via AccessMonster.com


.

 

[babs]

Ken,

any thoughts on which one or combination see 10/19 6PM post - I feel like I
am really close - thanks sooo much for helping!
Barb

Barb:

You need to sum the times up to and including the current date:

For all vacation time taken to date by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Workdate] <= #" & Format([Workdate]
,"yyyy-mm-dd") & "#")

For all vacation time taken within the year by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate])

For all vacation time taken on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And [Job #]
= " & [Job #])

For all vacation time taken within the year on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate] & " And [Job #] = " & [Job #])

Formatting the date in the ISO standard date notation format of YYYY-MM-DD
ensures that it will work on any system regardless of the local regional
setting for the date format. Substitute the real table name for YourTable.

In each case the expression is entered as a single line (they'll have been
split over several lines here by your newsreader) as the control's
ControlSource property of a text box in a form or report. In a query the
expressions can be used to return a computed column, omitting the leading =
sign in this case.

Rather than storing the field worker's name in the table its better to store
a numeric FieldWorkerID which references the unique FieldWorkerID primary key
of a separate FieldWorkers table with one row per field worker. This caters
for the possibility of two having the same names (it happens more often than
you'd think). It would also be better to have separate FirstName and
LastName fields in the FieldWorkers table. On a form you can use a combo box
for the FieldWorkerID, set up as follows so that it shows the name:

ControlSource: FieldWorkerID

RowSource: SELECT FieldWorkerID, FirstName & " " & LastName FROM
FieldWorkers ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

"Frank H" wrote:

--
Message posted via AccessMonster.com


.

 

[babs]

Ken, sorry for an additional post - but I tried the second suggestion and
added a parentheses here or there as needed but received and ERROR on the
form view in the control(textbox)-?? - so not sure of correct syntax to get
To sum the vacation time taken up to and including the current date for the
Present Year by each field worker:??
thanks again,
barb

Barb:

You need to sum the times up to and including the current date:

For all vacation time taken to date by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Workdate] <= #" & Format([Workdate]
,"yyyy-mm-dd") & "#")

For all vacation time taken within the year by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate])

For all vacation time taken on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And [Job #]
= " & [Job #])

For all vacation time taken within the year on each job by each field worker:

=DSum("[Vacation Time]", "[YourTable]", "[Field Worker] = """ & [Field Worker]
& """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And Year(
[Workdate] = " & Year([Workdate] & " And [Job #] = " & [Job #])

Formatting the date in the ISO standard date notation format of YYYY-MM-DD
ensures that it will work on any system regardless of the local regional
setting for the date format. Substitute the real table name for YourTable.

In each case the expression is entered as a single line (they'll have been
split over several lines here by your newsreader) as the control's
ControlSource property of a text box in a form or report. In a query the
expressions can be used to return a computed column, omitting the leading =
sign in this case.

Rather than storing the field worker's name in the table its better to store
a numeric FieldWorkerID which references the unique FieldWorkerID primary key
of a separate FieldWorkers table with one row per field worker. This caters
for the possibility of two having the same names (it happens more often than
you'd think). It would also be better to have separate FirstName and
LastName fields in the FieldWorkers table. On a form you can use a combo box
for the FieldWorkerID, set up as follows so that it shows the name:

ControlSource: FieldWorkerID

RowSource: SELECT FieldWorkerID, FirstName & " " & LastName FROM
FieldWorkers ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

see below data:

Field Worker Job # Workdate Vacation Time (hours) VH TotVacTak
CAGNEY, KEVIN 90180056 9/28/2009 0 12

CAGNEY, KEVIN 90180056 9/29/2009 0 12

CAGNEY, KEVIN 90180056 9/30/2009 0 12

CAGNEY, KEVIN 90180056 10/1/2009 8 12
8
CAGNEY, KEVIN 90180056 10/2/2009 4 12

the 1st column of VH(vacation taken) shows the acutal date the vacation
taken. the second column is the DSUM- formula - text box (passed back in
based off the the dsum by man name and date within 2009) the total is
correct - BUT DO NOT LIKE that the dates prior to it taken(9/28-9/30/09) show
up as having used 12 hours of vacaction - anyway to add code in to it show a
Running Actual total -

Thanks sooo much for helping!
Barb

"Frank H" wrote:

--
Message posted via AccessMonster.com


.

 

[babs]

thanks Ken - the totals as you go look good - one odd error in middle of data
- who knows.

when I am in the datasheet view and add vacation time I have to hit refresh
All for calculations to update - anyway to refresh in the event procedure _
not sure where or what code???

thanks a ton!!!!
Barb

Barb:

This is what I've done:

1. I created a query based on a table of my own which has similar data and
in the query named the columns to match your column names.

2. I saved the query as: JeffTime Card MD Query.

3. I created a continuous view form based on the query and added an unbound
text box with its ControlSource exactly as I posted, i.e.

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]","[Man Name] = """ & [Man
Name] & """ And [Workdate] <= #" & Format([Workdate],"yyyy-mm-dd") & "# And
Year([Workdate]) = " & Year([Workdate]))

It worked perfectly. The only place it reports an error is in the empty row
for entering a new record at the bottom of the form if the query is an
updatable one, but the running totals in each existing row are exactly as
expected. The error in the new row disappears once data is entered in the
row, and shows again of course in the new blank row which appears at the
bottom ready for another new record. If I change the query to make it non-
updatable, then no blank row shows at the bottom of the form of course, so no
error shows.

One possible reason for the error is that the expression you are seeing above
has probably been split over several lines (I'm seeing it in Access Monster
as three lines). In the ControlSource property its entered as a single line
of course. It could be that when you've copied and pasted it from here
you've lost spaces from the original expression where the lines have broken.
Here it is broken down manually over a number of lines, deliberately breaking
each line where there is no space, so if you copy and paste it and then
simply delete each line break, it should be OK:

=DSum("[HoursVacaCor]","[JeffTime Card MD Query]",
"[Man Name] = """ & [Man Name] & ""
" And [Workdate] <= #" & Format([Workdate],"yyyy-
mm-dd") & "# And Year([Workdate]) = " & Year([Workdate]))

Tip: Right click and 'Zoom' the ControlSource property before pasting it in.
That way you'll see all the four lines together.

See if that does the trick.

BTW I also tried the query whose SQL I posted and that works too.

Ken Sheridan
Stafford, England

Ken, sorry for an additional post - but I tried the second suggestion and
added a parentheses here or there as needed but received and ERROR on the
form view in the control(textbox)-?? - so not sure of correct syntax to get
To sum the vacation time taken up to and including the current date for the
Present Year by each field worker:??
thanks again,
barb

Barb:

[quoted text clipped - 82 lines]

"Frank H" wrote:

--
Message posted via AccessMonster.com


.