Prev: Trim Time off Date
Next: Grand Total Time
From: fossy on 25 Feb 2010 19:36
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.
From: John Spencer on 26 Feb 2010 08:04
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.
 | 
Pages: 1
Prev: Trim Time off Date
Next: Grand Total Time