Re: Subtract 2 fields & count if <60

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Bob. I am getting it. However, when I went deeper, something odd
happened. This Anesthesiologist - Chiu, has 6 total cases. Looking at the
data in teh table I can see that in 5 of those cases he was under 60 minutes
and in the sixth case, the fields are blank because there was no antibiotic
documented. So if I build a query that looks like this:

SELECT [Anesthesiologist]
, abs(Count(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<=60)) AS
[under 60]
, Abs(Count(DateDiff("n",[Procedure_start_time],[ABX_1_Admin_Time])>60)) as
[Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

I should get 5 in the Under60 field and I guess 0 in the Over60 field
(although I need it to indicate 1 if it is null). However, the above query
returns a 5 in both the fields?

"Bob Barrows" wrote:

G. wrote:
Allright guys, very funny banter.

John, I made different attempts to get the SQL statement to work but
all to no avail. I can't get what I am doing wrong. I broke it down
many ways and tried different things to understand. Perhaps the
simplest to present to you for your (or any one elses) follow up help
would be this - I took a simple piece of the SQL:

SELECT [Anesthesiologist]

,abs(Count(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<60))
AS [under 60]

FROM Surgery

WHERE [Anesthesiologist] IN ("chiu");

I get an error message that says I tried to execute a query that does
not include the specified expression Anasthesiologist as part of an
aggregate function.

Well, yes. That's pretty much what you are doing wrong here. If you
aren't aggregating the Anesthesiologist field, then you need to be
grouping by it if you want to include it in the select clause.

In this particular case, your WHERE clause is limiting the result to a
single Anesthesiologist ("chiu") so you don't even need Anesthesiologist
in the select clause. This query should run without error:

SELECT
abs(Count(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<60))
AS
[under 60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu");


If you absolutely must include Anesthesiologist in the SELECT clause,
then you must add a GROUP BY clause:

SELECT [Anesthesiologist]
, abs(Count(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<60))
AS
[under 60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

Alternatively, you can aggregate it:

SELECT First([Anesthesiologist])
, abs(Count(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<60))
AS
[under 60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")



--
HTH,
Bob Barrows



.

Relevant Pages

  • Re: Subtract 2 fields & count if <60
    ... "Bob Barrows" wrote: ... So if I build a query ... FROM Surgery ... your WHERE clause is limiting the result ...
    (microsoft.public.access.queries)
  • Re: Subtract 2 fields & count if <60
    ... So if I build a query ... FROM Surgery ... your WHERE clause is limiting the result ... Alternatively, you can aggregate it: ...
    (microsoft.public.access.queries)
  • Re: Best Wishes to Bob Benjamin......................
    ... surgery provides a more comfortable life. ... allow Bob a full recovery and a return to business as usual. ... through surgery to help control and relieve the worse symptoms of the ...
    (rec.music.artists.springsteen)
  • Best Wishes to Bob Benjamin......................
    ... Heartfelt best wishes for success to Bob Benjamin, ... control the debilitating symptoms brought on by the still incurable ... as well as the nature of his surgery will be ...
    (rec.music.artists.springsteen)
  • Re: Its Surgery Time!!!
    ... bob wrote: ... people and is an alternative to surgery. ... $25.00 each) to finally get the pain stopped, ...
    (rec.food.cooking)