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

[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

 

[Piet Linden]

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

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.

 

[RichKorea]

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.

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