Subtract 2 fields & count if <60

[G.]

I can do this with multiple transitory queries or exporting to Excel and then
importin gback to Acces or by doing Make Table queries, etc. But, there must
be an easier way I am sure.

I have a "Surgery" table with Name of Anesthesiologist, Antibiotic Admin
Time, Surgery Incision Time, and other fields.

I need to create a query that gives me the count of the number of times that
the time difference between the incision time and antibiotic admin time is
less than 60 for each anasthesiologist that I define in the criteria area of
the query.

The anesthesiologists need to be grouped because the same anesthesiologist
could have been on multiple procedures (i.e. I do not have a separate table
for them).

This calculated field in a query is going to be one of a few. Other fields
will be the total # of procedures, # of times over the 60 minute mark, % of
time over the 60 minute mark, average amount of time when over the 60 minute
mark, avg amount of time when under the 60 minute mark, Overall avg. time
difference between incision time and antibiotic admin., and maybe a couple
others.

I can piece this together but it is ugly. Please help, I know there must be
something simpler but the more I think about it the more convoluted my
solutions become.

Thank you for your help.

 

[John Spencer MVP]

Assuming your field names are PersonName (Anesthesiologist), IncisionTime and
AntibioticTime then you could use a query like the following.

SELECT [PersonName]
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60)) as Under60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60)) as Over60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60))/
Count([PersonName]) as PercentOver60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60))/
Count([PersonName]) as PercentUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])<60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])>=60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgOver60
, Count([PersonName]) as NumberOfProcedures
, Avg DateDiff("n",[IncisionTime],[AntibioticTime]) as AvgTime

FROM Surgery

WHERE [PersonName] in ("Bob","Ted","Carol","Alice")

GROUP BY [PersonName]

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

 

[Dale Fye]

John,

You're aging yourself. Shouldn't that be:

IN ("Bob", "Carol", "Ted", "Alice")

;-)

----
HTH
Dale

Assuming your field names are PersonName (Anesthesiologist), IncisionTime and
AntibioticTime then you could use a query like the following.

SELECT [PersonName]
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60)) as Under60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60)) as Over60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60))/
Count([PersonName]) as PercentOver60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60))/
Count([PersonName]) as PercentUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])<60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])>=60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgOver60
, Count([PersonName]) as NumberOfProcedures
, Avg DateDiff("n",[IncisionTime],[AntibioticTime]) as AvgTime

FROM Surgery

WHERE [PersonName] in ("Bob","Ted","Carol","Alice")

GROUP BY [PersonName]

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

I can do this with multiple transitory queries or exporting to Excel and then
importin gback to Acces or by doing Make Table queries, etc. But, there must
be an easier way I am sure.

I have a "Surgery" table with Name of Anesthesiologist, Antibiotic Admin
Time, Surgery Incision Time, and other fields.

I need to create a query that gives me the count of the number of times that
the time difference between the incision time and antibiotic admin time is
less than 60 for each anasthesiologist that I define in the criteria area of
the query.

The anesthesiologists need to be grouped because the same anesthesiologist
could have been on multiple procedures (i.e. I do not have a separate table
for them).

This calculated field in a query is going to be one of a few. Other fields
will be the total # of procedures, # of times over the 60 minute mark, % of
time over the 60 minute mark, average amount of time when over the 60 minute
mark, avg amount of time when under the 60 minute mark, Overall avg. time
difference between incision time and antibiotic admin., and maybe a couple
others.

I can piece this together but it is ugly. Please help, I know there must be
something simpler but the more I think about it the more convoluted my
solutions become.

Thank you for your help.

 

[John Spencer]

Well, it could be. On the other hand are you sure that these are
heterosexual associations?

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

John,

You're aging yourself. Shouldn't that be:

IN ("Bob", "Carol", "Ted", "Alice")

;-)

----
HTH
Dale

Assuming your field names are PersonName (Anesthesiologist), IncisionTime and
AntibioticTime then you could use a query like the following.

SELECT [PersonName]
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60)) as Under60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60)) as Over60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60))/
Count([PersonName]) as PercentOver60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60))/
Count([PersonName]) as PercentUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])<60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])>=60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgOver60
, Count([PersonName]) as NumberOfProcedures
, Avg DateDiff("n",[IncisionTime],[AntibioticTime]) as AvgTime

FROM Surgery

WHERE [PersonName] in ("Bob","Ted","Carol","Alice")

GROUP BY [PersonName]

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

I can do this with multiple transitory queries or exporting to Excel and then
importin gback to Acces or by doing Make Table queries, etc. But, there must
be an easier way I am sure.

I have a "Surgery" table with Name of Anesthesiologist, Antibiotic Admin
Time, Surgery Incision Time, and other fields.

I need to create a query that gives me the count of the number of times that
the time difference between the incision time and antibiotic admin time is
less than 60 for each anasthesiologist that I define in the criteria area of
the query.

The anesthesiologists need to be grouped because the same anesthesiologist
could have been on multiple procedures (i.e. I do not have a separate table
for them).

This calculated field in a query is going to be one of a few. Other fields
will be the total # of procedures, # of times over the 60 minute mark, % of
time over the 60 minute mark, average amount of time when over the 60 minute
mark, avg amount of time when under the 60 minute mark, Overall avg. time
difference between incision time and antibiotic admin., and maybe a couple
others.

I can piece this together but it is ugly. Please help, I know there must be
something simpler but the more I think about it the more convoluted my
solutions become.

Thank you for your help.

 

[G.]

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, it could be. On the other hand are you sure that these are
heterosexual associations?

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

John,

You're aging yourself. Shouldn't that be:

IN ("Bob", "Carol", "Ted", "Alice")

;-)

----
HTH
Dale

Assuming your field names are PersonName (Anesthesiologist), IncisionTime and
AntibioticTime then you could use a query like the following.

SELECT [PersonName]
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60)) as Under60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60)) as Over60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])>=60))/
Count([PersonName]) as PercentOver60
, Abs(Count(DateDiff("n",[IncisionTime],[AntibioticTime])<60))/
Count([PersonName]) as PercentUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])<60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgUnder60
, Avg(IIF(DateDiff("n",[IncisionTime], [AntibioticTime])>=60),
DateDiff("n",[IncisionTime],[AntibioticTime]), Null) as AvgOver60
, Count([PersonName]) as NumberOfProcedures
, Avg DateDiff("n",[IncisionTime],[AntibioticTime]) as AvgTime

FROM Surgery

WHERE [PersonName] in ("Bob","Ted","Carol","Alice")

GROUP BY [PersonName]

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

G. wrote:
I can do this with multiple transitory queries or exporting to Excel and then
importin gback to Acces or by doing Make Table queries, etc. But, there must
be an easier way I am sure.

I have a "Surgery" table with Name of Anesthesiologist, Antibiotic Admin
Time, Surgery Incision Time, and other fields.

I need to create a query that gives me the count of the number of times that
the time difference between the incision time and antibiotic admin time is
less than 60 for each anasthesiologist that I define in the criteria area of
the query.

The anesthesiologists need to be grouped because the same anesthesiologist
could have been on multiple procedures (i.e. I do not have a separate table
for them).

This calculated field in a query is going to be one of a few. Other fields
will be the total # of procedures, # of times over the 60 minute mark, % of
time over the 60 minute mark, average amount of time when over the 60 minute
mark, avg amount of time when under the 60 minute mark, Overall avg. time
difference between incision time and antibiotic admin., and maybe a couple
others.

I can piece this together but it is ugly. Please help, I know there must be
something simpler but the more I think about it the more convoluted my
solutions become.

Thank you for your help.

 

[Bob Barrows]

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")

 

[G.]

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?

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")

 

[Bob Barrows]

I've got to go to a meeting right now. I'll come back to this if no one
else replies in the meantime.

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?

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")

 

[G.]

Thanks to all who are helping. I truly appreciate the learning opportunity
and am very greatful. To clarify, I need a few columns of info but I am now
concentrating on a filed that will count the # of times the difference
between two times are over 60 minutes and under 60 for a specified # of
people.

I originally thought I needed an IF statement. Something that would return
the count if the difference between the times was <=60 and then >60.
Something like this:

Name Under60 Over60
A 5 1
B 18 3
C 45 5

Thanks again.

I've got to go to a meeting right now. I'll come back to this if no one
else replies in the meantime.

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?

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")

 

[Bob Barrows]

You're almost there. Instead of count, use sum. Like this (I don't think
the abs function is needed):

SELECT [Anesthesiologist]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<=60,1,0)
) AS
[under 60]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])>60,1,0))
AS
[Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

I'm not sure why it should indicate 1 if null. let's look at your
desired results again:

Name Under60 Over60
A 5 1
B 18 3
C 45 5

I assume the "A" record is Chiu. What do the source records look like?
Are both procedure_start_time and abx_1_admin_time Null for that record
where you want to see 1? Is the idea that if 1 or both of them contains
null that you want the record to be considered Over60?

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?

 

[G.]

Thanks Bob, yes, the idea is that if one or both fields are null within a
record, that record should be counted in the over60.

I'll try the revised SQL - thanks again.

You're almost there. Instead of count, use sum. Like this (I don't think
the abs function is needed):

SELECT [Anesthesiologist]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<=60,1,0)
) AS
[under 60]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])>60,1,0))
AS
[Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

I'm not sure why it should indicate 1 if null. let's look at your
desired results again:

Name Under60 Over60
A 5 1
B 18 3
C 45 5

I assume the "A" record is Chiu. What do the source records look like?
Are both procedure_start_time and abx_1_admin_time Null for that record
where you want to see 1? Is the idea that if 1 or both of them contains
null that you want the record to be considered Over60?

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]

Well, that will require a revision of the SQL I supplied to use the Nz
function:

SELECT [Anesthesiologist],
Sum(
IIf(
DateDiff(
"n",
[procedure_start_time],
[abx_1_admin_time]
)<=60,
1,
0)
) AS [under 60],
Sum(
IIf(
DateDiff(
"n",
Nz([procedure_start_time],0),
Nz([abx_1_admin_time],Date())
)>60,
1,
0)
) AS [Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

Thanks Bob, yes, the idea is that if one or both fields are null
within a record, that record should be counted in the over60.

I'll try the revised SQL - thanks again.

You're almost there. Instead of count, use sum. Like this (I don't
think the abs function is needed):

SELECT [Anesthesiologist]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<=60,1,0)
) AS
[under 60]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])>60,1,0))
AS
[Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

I'm not sure why it should indicate 1 if null. let's look at your
desired results again:

Name Under60 Over60
A 5 1
B 18 3
C 45 5

I assume the "A" record is Chiu. What do the source records look
like? Are both procedure_start_time and abx_1_admin_time Null for
that record where you want to see 1? Is the idea that if 1 or both
of them contains null that you want the record to be considered
Over60?

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?

 

[G.]

You are a stinkin' genious. Thank you. I very sincerely appreciate the help
you gave, time you put in, and knowledge I received.

Well, that will require a revision of the SQL I supplied to use the Nz
function:

SELECT [Anesthesiologist],
Sum(
IIf(
DateDiff(
"n",
[procedure_start_time],
[abx_1_admin_time]
)<=60,
1,
0)
) AS [under 60],
Sum(
IIf(
DateDiff(
"n",
Nz([procedure_start_time],0),
Nz([abx_1_admin_time],Date())
)>60,
1,
0)
) AS [Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

Thanks Bob, yes, the idea is that if one or both fields are null
within a record, that record should be counted in the over60.

I'll try the revised SQL - thanks again.

You're almost there. Instead of count, use sum. Like this (I don't
think the abs function is needed):

SELECT [Anesthesiologist]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])<=60,1,0)
) AS
[under 60]
,
Sum(IIf(DateDiff("n",[procedure_start_time],[abx_1_admin_time])>60,1,0))
AS
[Over60]
FROM Surgery
WHERE [Anesthesiologist] IN ("chiu")
GROUP BY [Anesthesiologist] ;

I'm not sure why it should indicate 1 if null. let's look at your
desired results again:
Name Under60 Over60
A 5 1
B 18 3
C 45 5

I assume the "A" record is Chiu. What do the source records look
like? Are both procedure_start_time and abx_1_admin_time Null for
that record where you want to see 1? Is the idea that if 1 or both
of them contains null that you want the record to be considered
Over60?


G. wrote:
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?