how to build expression with conditins and totals

Ok... I have 3 tables (tbJOBS, tbDept1, tbDept1_DwnTime) and a query that
returns: all the jobs ran on range of days ( criteria: between day1 and
day2); the time it took to set up; the time it tooke to produce; and, the
down time for each job.

I was asked to seperate the SetUp's DownTime from Production's DownTime so I
added a check box on the "tbDept1_DwnTime" table. Now, my boss can tell if
the down time belongs to the setup time or the production time. This works
fine.

Now, I'm being asked to have the report (query), automatically substract the
downtime (setup) from the total setup time and substract the downtime
(production) from the total production time.

I need a way to build a expression that know what type of downtime is and
then substract it from the setup time OR the production time.

Any suggestion will help thank you.
Fossy.

It would help if you posted your current query. As a guess you need
expressions like the following in your query. I'm assuming you are using a
totals query. If not, then change the expression by removing the Sum operators.

Sum([SetUpTime])-Sum(IIF([CheckBoxField]=True,[DownTime],0))

Sum([ProductionTime])-Sum(IIF([CheckBoxField]=False,DownTime,0))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

fossy wrote:
Ok... I have 3 tables (tbJOBS, tbDept1, tbDept1_DwnTime) and a query that
returns: all the jobs ran on range of days ( criteria: between day1 and
day2); the time it took to set up; the time it tooke to produce; and, the
down time for each job.

I was asked to seperate the SetUp's DownTime from Production's DownTime so I
added a check box on the "tbDept1_DwnTime" table. Now, my boss can tell if
the down time belongs to the setup time or the production time. This works
fine.

Now, I'm being asked to have the report (query), automatically substract the
downtime (setup) from the total setup time and substract the downtime
(production) from the total production time.

I need a way to build a expression that know what type of downtime is and
then substract it from the setup time OR the production time.

Any suggestion will help thank you.
Fossy.