Sum one field if another field is equal to something

  • Thread starter Thread starter Robert G. Flade, RN, MS
  • Start date Start date
R

Robert G. Flade, RN, MS

I have a simple flat table that has fields like date, description, work type,
duration, etc.

I want to have the report list all the entries in date order but at the
bottom of the report I want it to total all the "duration" by "work type".
In one day there may be many different entries of each of the diffierent work
types (DK, Work Order, & Other). I don't want a total for each day nor do I
want them grouped by the work type. . . I need the report to be in date/time
order.

So I want the following:
Report:
07/01/2009 DK 0:30 *^*^*%(%
07/01/2009 Work Order 1:00 *%^*&)&
07/01/2000 Work Order 4:30 &(&^()&*
...
07/10/2009 DK 2:45 (^*%(*%

Footer on last page:
Work type: DK <total time>
Work type: Work Order <total time>
Work type: Other <total time>

Since there is no SUMIF statement - how can I sum those hours by those work
types?

Thanks
Bob

--
Robert G. Flade, RN, MS
Director - Emergency Departments
The Hospital of Central CT
at New Britain General & Bradley Memorial
100 Grand Street/PO Box 100
New Britain, CT 06050-0100
 
Robert said:
I have a simple flat table that has fields like date, description, work type,
duration, etc.

I want to have the report list all the entries in date order but at the
bottom of the report I want it to total all the "duration" by "work type".
In one day there may be many different entries of each of the diffierent work
types (DK, Work Order, & Other). I don't want a total for each day nor do I
want them grouped by the work type. . . I need the report to be in date/time
order.

So I want the following:
Report:
07/01/2009 DK 0:30 *^*^*%(%
07/01/2009 Work Order 1:00 *%^*&)&
07/01/2000 Work Order 4:30 &(&^()&*
...
07/10/2009 DK 2:45 (^*%(*%

Footer on last page:
Work type: DK <total time>
Work type: Work Order <total time>
Work type: Other <total time>

Since there is no SUMIF statement - how can I sum those hours by those work
types?
Click to expand...


This kind of thing is only managable for very few different
conditions:

=Sum(IIf([work type] = "DK", duration, 0))

Better would be to create a Totals type query that
aggregates the durations for each work type. Then put the
subreport into your existing report.
 
You could use a sub-report to get the values. However, you can use an
expression like the following as the control source. One problem is that if
your Durations are being stored in a DateTime field, then you will get a date
and time returned when the sum of the times exceed 24 hours.

=Sum(IIF([WorkType]="DK",[TimeDuration],0))

If your TimeDuration field is a datetime field and you don't want to change it
to save units of time (for instance, the number of minutes) then you can try

=Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))/60
which will return the hours and fractions of an hour.

You can extend that to get something that looks like hours and minutes
= Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))\60
& ":" &
Format(Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))
mod 60,"00")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
When I try this all I get is "#Error" (I attempted this expression before but
thought I was attempting to do something I shouldn't and that is why I was
getting the "#Error").

--
Robert G. Flade, RN, MS
Director - Emergency Departments
The Hospital of Central CT
at New Britain General & Bradley Memorial
100 Grand Street/PO Box 100
New Britain, CT 06050-0100



Marshall Barton said:
Robert said:
I have a simple flat table that has fields like date, description, work type,
duration, etc.

I want to have the report list all the entries in date order but at the
bottom of the report I want it to total all the "duration" by "work type".
In one day there may be many different entries of each of the diffierent work
types (DK, Work Order, & Other). I don't want a total for each day nor do I
want them grouped by the work type. . . I need the report to be in date/time
order.

So I want the following:
Report:
07/01/2009 DK 0:30 *^*^*%(%
07/01/2009 Work Order 1:00 *%^*&)&
07/01/2000 Work Order 4:30 &(&^()&*
...
07/10/2009 DK 2:45 (^*%(*%

Footer on last page:
Work type: DK <total time>
Work type: Work Order <total time>
Work type: Other <total time>

Since there is no SUMIF statement - how can I sum those hours by those work
types?
Click to expand...


This kind of thing is only managable for very few different
conditions:

=Sum(IIf([work type] = "DK", duration, 0))

Better would be to create a Totals type query that
aggregates the durations for each work type. Then put the
subreport into your existing report.
Click to expand...
 
This is someone elses database and I dno't have control over the structure.
I attempted the:
= Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))\60
& ":" &
Format(Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))
mod 60,"00")

But just get "#Error"
--
Robert G. Flade, RN, MS
Director - Emergency Departments
The Hospital of Central CT
at New Britain General & Bradley Memorial
100 Grand Street/PO Box 100
New Britain, CT 06050-0100



John Spencer said:
You could use a sub-report to get the values. However, you can use an
expression like the following as the control source. One problem is that if
your Durations are being stored in a DateTime field, then you will get a date
and time returned when the sum of the times exceed 24 hours.

=Sum(IIF([WorkType]="DK",[TimeDuration],0))

If your TimeDuration field is a datetime field and you don't want to change it
to save units of time (for instance, the number of minutes) then you can try

=Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))/60
which will return the hours and fractions of an hour.

You can extend that to get something that looks like hours and minutes
= Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))\60
& ":" &
Format(Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))
mod 60,"00")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a simple flat table that has fields like date, description, work type,
duration, etc.

I want to have the report list all the entries in date order but at the
bottom of the report I want it to total all the "duration" by "work type".
In one day there may be many different entries of each of the diffierent work
types (DK, Work Order, & Other). I don't want a total for each day nor do I
want them grouped by the work type. . . I need the report to be in date/time
order.

So I want the following:
Report:
07/01/2009 DK 0:30 *^*^*%(%
07/01/2009 Work Order 1:00 *%^*&)&
07/01/2000 Work Order 4:30 &(&^()&*
...
07/10/2009 DK 2:45 (^*%(*%

Footer on last page:
Work type: DK <total time>
Work type: Work Order <total time>
Work type: Other <total time>

Since there is no SUMIF statement - how can I sum those hours by those work
types?

Thanks
Bob
Click to expand...
Click to expand...
 
If your time duration is ever null or any other value that cannot be
interpreted as a time you will get an error.

Start off simple and then introduce complexity. So as a first step you might try

=Sum(IIF([WorkType]="DK" and IsDate([TimeDuration]),
DateDiff("n",0,TimeValue([TimeDuration])),0))

You never told us the field type of your time duration field, the exact name
of the field, and what is being stored in the field. All of those could be
crucial to finding a solution or giving you the best solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
"Duration" is a Date/Time field set to "Short Time"
"Work Type" is a Text field (look up field that is NOT linked to another
table - they defined the three types up front).

It seems like this shoudl be so simple - I just cant see what I'm doing
wrong. . .
--
Robert G. Flade, RN, MS
Director - Emergency Departments
The Hospital of Central CT
at New Britain General & Bradley Memorial
100 Grand Street/PO Box 100
New Britain, CT 06050-0100



John Spencer said:
You could use a sub-report to get the values. However, you can use an
expression like the following as the control source. One problem is that if
your Durations are being stored in a DateTime field, then you will get a date
and time returned when the sum of the times exceed 24 hours.

=Sum(IIF([WorkType]="DK",[TimeDuration],0))

If your TimeDuration field is a datetime field and you don't want to change it
to save units of time (for instance, the number of minutes) then you can try

=Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))/60
which will return the hours and fractions of an hour.

You can extend that to get something that looks like hours and minutes
= Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))\60
& ":" &
Format(Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))
mod 60,"00")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a simple flat table that has fields like date, description, work type,
duration, etc.

I want to have the report list all the entries in date order but at the
bottom of the report I want it to total all the "duration" by "work type".
In one day there may be many different entries of each of the diffierent work
types (DK, Work Order, & Other). I don't want a total for each day nor do I
want them grouped by the work type. . . I need the report to be in date/time
order.

So I want the following:
Report:
07/01/2009 DK 0:30 *^*^*%(%
07/01/2009 Work Order 1:00 *%^*&)&
07/01/2000 Work Order 4:30 &(&^()&*
...
07/10/2009 DK 2:45 (^*%(*%

Footer on last page:
Work type: DK <total time>
Work type: Work Order <total time>
Work type: Other <total time>

Since there is no SUMIF statement - how can I sum those hours by those work
types?

Thanks
Bob
Click to expand...
Click to expand...
 
Robert said:
When I try this all I get is "#Error" (I attempted this expression before but
thought I was attempting to do something I shouldn't and that is why I was
getting the "#Error").
Click to expand...


Are you sure that duration is a date/time field in the
report's recorsource table/query? It will not work if
duration is a control in the report. If the report's record
source is a query, check to make sure you did not do
something to cause the query to convert it to a text string.
 
John, is there any way to account for null? Trying to add dive time duration
for different divers, some are null....

John Spencer said:
If your time duration is ever null or any other value that cannot be
interpreted as a time you will get an error.

Start off simple and then introduce complexity. So as a first step you might try

=Sum(IIF([WorkType]="DK" and IsDate([TimeDuration]),
DateDiff("n",0,TimeValue([TimeDuration])),0))

You never told us the field type of your time duration field, the exact name
of the field, and what is being stored in the field. All of those could be
crucial to finding a solution or giving you the best solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This is someone elses database and I dno't have control over the structure.
I attempted the:
= Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))\60
& ":" &
Format(Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue([TimeDuration])),0))
mod 60,"00")

But just get "#Error"
Click to expand...
Click to expand...
 
Try this statement
=Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue(Nz([TimeDuration],#0:0:0#))),0))

If that doesn't work then you will probably need a custom function to handle
the situation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John, that worked!

John Spencer said:
Try this statement
=Sum(IIF([WorkType]="DK",DateDiff("n",0,TimeValue(Nz([TimeDuration],#0:0:0#))),0))

If that doesn't work then you will probably need a custom function to handle
the situation.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John, is there any way to account for null? Trying to add dive time duration
for different divers, some are null....

:
Click to expand...
Click to expand...
 
Back
Top