Sum one field if another field is equal to something

  • Thread starter Robert G. Flade, RN, MS
  • 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
 
M

Marshall Barton

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?


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.
 
J

John Spencer

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
 
R

Robert G. Flade, RN, MS

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?


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.
 
R

Robert G. Flade, RN, MS

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
 
J

John Spencer

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
 
R

Robert G. Flade, RN, MS

"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
 
M

Marshall Barton

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").


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.
 
M

mattsmom

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"
 
J

John Spencer

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
 
M

mattsmom

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....

:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top