Count and second count 1

[peterb777]

Please help:

I've built a query that reports the number of request and average hours for a given week range, result:

Week Time Requests
40 10.60 76
41 5.09 65

Where "Week" is the work week, "Time" is the average hours per request and "Request" is the total request. I want to add a column to show the number of request over 24 hrs.

Week Time Requests Over24Hrs
40 10.60 76 ?
41 5.09 65 ?

I can't believe this is so difficult. Any help is greatly appreciated. SQL as follows:

***Begin SQL***
SELECT DatePart("ww",[tblTTLive]![Complete Date&Time]) AS Week, Avg(DateDiff("n",[tblTTLive]![Start Date&Time],[tblTTLive]![Complete Date&Time])/60) AS [Time], Count(DatePart("ww",[tblTTLive]![Complete Date&Time])) AS Requests
FROM tblTTLive
WHERE (((tblTTLive.Detail) Not Like "PO") AND ((tblTTLive.BL) Not Like "*Dumm*" And (tblTTLive.BL) Is Not Null)) OR (((tblTTLive.Detail) Not Like "PO REQUEST"))
GROUP BY DatePart("ww",[tblTTLive]![Complete Date&Time])
HAVING (((DatePart("ww",[tblTTLive]![Complete Date&Time])) Is Not Null And (DatePart("ww",[tblTTLive]![Complete Date&Time]))>=[Start Week] And (DatePart("ww",[tblTTLive]![Complete Date&Time]))<=[End Week]) AND ((Avg(DateDiff("n",[tblTTLive]![Start Date&Time],[tblTTLive]![Complete Date&Time])/60)) Is Not Null));

***End SQL***

 

[traingamer]

A column something like

sum(IIf(DateDiff("h",[StartTime],[Completetime])>=24,1,0))

which basically checks to see if 24 or more hours have passed, and , if so, assigns a value of 1.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[peterb777]

Thanks, that worked great.