Listing cases with no data as zero, and then calculating a total

  • Thread starter Thread starter RichKorea
  • Start date Start date
R

RichKorea

I’m putting together a service database where I keep track of engineer’s
service time, and I want to have a monthly report that reports all of the
service time by individual engineer, with totals grand totals at the end. My
data is setup with a table of all of the engineers and a table of service
tasks. My first try at a report ran a query against the task table, which
would allow each engineer to be listed on a report with a total in the report
footer. The problem comes in when an engineer had no task time during a
month (on a non-task assignment). For those cases, the query wouldn’t pick
up those engineers with no tasks. I’ve changed to basing the report on a
query of all of the engineers and connecting to a sub-report of task time for
each engineer, but that has two difficulties I haven’t been able to figure
out how to over come.

Difficulty #1 – If the engineer has no task time, I want to show zero in the
task categories (travel time, wait time, direct work time, etc.), but the
sub-report just shows a blank space (the query for the sub-report returns
nothing, so there’s nothing to display).

Difficulty #2 – I want a total in the report footer, but I can’t figure out
how to sum the totals from the individual sub-reports. I thought about
running another query just for the report footer to get the totals, but I
wanted to see if there was a way to get the data from the sub-reports.

Thanks,
Rich
 
I’m putting together a service database where I keep track of engineer’s
service time, and I want to have a monthly report that reports all of the
service time by individual engineer, with totals grand totals at the end. My
data is setup with a table of all of the engineers and a table of service
tasks.  My first try at a report ran a query against the task table, which
would allow each engineer to be listed on a report with a total in the report
footer.  The problem comes in when an engineer had no task time during a
month (on a non-task assignment).  For those cases, the query wouldn’t pick
up those engineers with no tasks.  I’ve changed to basing the report on a
query of all of the engineers and connecting to a sub-report of task timefor
each engineer, but that has two difficulties I haven’t been able to figure
out how to over come.

Difficulty #1 – If the engineer has no task time, I want to show zero in the
task categories (travel time, wait time, direct work time, etc.), but the
sub-report just shows a blank space (the query for the sub-report returns
nothing, so there’s nothing to display).

Difficulty #2 – I want a total in the report footer, but I can’t figure out
how to sum the totals from the individual sub-reports.  I thought about
running another query just for the report footer to get the totals, but I
wanted to see if there was a way to get the data from the sub-reports.

Thanks,
Rich
Click to expand...

use an outer join in the query instead of an inner join. Then the
engineers with no hours will not fall out of the query result.
 
Piet Linden said:
use an outer join in the query instead of an inner join. Then the
engineers with no hours will not fall out of the query result.
Click to expand...

I switched from INNER JOIN to LEFT JOIN, added an IIF Null to put a zero
value in for the lines with nothing to report, and I'm all set.

Thanks
 
Back
Top