Prev: Report with Monthly rows, Yearly columns & 3 pieces of data per mo
Next: Memo field does not grow - tried usual solutions
From: RichKorea on 30 Jun 2008 00:56


"Piet Linden" wrote:

> On Jun 29, 10:29 am, RichKorea <RichKo...(a)discussions.microsoft.com>
> wrote:
> > 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
>
> 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
 | 
Pages: 1
Prev: Report with Monthly rows, Yearly columns & 3 pieces of data per mo
Next: Memo field does not grow - tried usual solutions