Query Calculations

[stuartandwil]

i use two calculations to give me job downtimes, to calculate a job that is still in progress and give me the downtime so far I use

DateDiff("n",[start],Now())

to calculate the downtime of a job that is completed i use

DateDiff("n",[start],[complete])

The above are two seperate queries.

What I want is a report from the query that will give me both calculations for the jobs in one report. So in the list i will get all jobs but the ones not completed will give the downtime up to the time of the report. Is this possible by using 1 query to give me boith results?

Many thanks

 

[Gates Is Antichrist]

Hav e you though of nexting queries? You know, the source for a query isn't necessarily a table; it could be another query (i.e. "subquery").

You could also build the results together via make table/append table.

But I'm not aware of any way that you can have multiple input recordsources for a report.

 

[bat17]

You could put the queries in as subreports.

HTh

Peter

 

[SydneyGeek]

Are the 2 calculations really in 2 separate queries, or are they just 2 different calculation fields? You could build a single query with fields like:
JobID, JobDescription, Start, Complete, and then a single calculation field, Downtime:Iif(IsBlank([Complete]),DateDiff("n",[start],Now()) ,DateDiff("n",[start],[complete]) )
Then you have the full downtime calculation in one field and can filter on the presence / absence of a value in the [Complete] field.

Regards
Denis

 

[stuartandwil]

Substituted isblank (worksheet function, I think) for isnull and I think that does the trick.

Many thanks