trouble with average calculations in query 2

[DLynnTX]

Here is what the final report needs to look like:

SurgeonName) (Start date-end date)

Surgery type ¦ No. of Procedures ¦ Avg Surgery time ¦ % Pediatric ¦ Avg length of stay

And it’s the Avg surgery time & % Pediatric that I’m having trouble with.

I have a surgery table that lists the surgeries performed for patients. I need to pull a report that takes the surgery type (Stats) and shows the # of that type of procedure performed and the average surgery time (calculated field using OR Start and OR End times), % of pediatric (i.e. age < 18) and average length of stay (Total LOS). This report is by surgeon (surgeon ID) and based on specific dates stored in a form named “Report Date Form”.

I have the following query which works fine to gather the information:

SELECT Surgery.Stats, Surgery.PatientID, Surgery.[Surgeon ID], (24*Int(Sum([OR End]-[OR Start]))+Format(Sum([OR End]-[OR Start]),'h')) & Format(Sum([OR End]-[OR Start]),':nn:ss') AS TotalTime, (([Yrs]*12)+[Mos])/12 AS Age, [Discharge Information].[Total LOS], Surgery.ORDate
FROM ([Patient Info] INNER JOIN Surgery ON [Patient Info].PatientID = Surgery.PatientID) INNER JOIN [Discharge Information] ON [Patient Info].PatientID = [Discharge Information].PatientID
GROUP BY Surgery.Stats, Surgery.PatientID, Surgery.[Surgeon ID], (([Yrs]*12)+[Mos])/12, [Discharge Information].[Total LOS], Surgery.ORDate
HAVING (((Surgery.Stats) Is Not Null) AND ((Surgery.[Surgeon ID])=[Forms]![Report Date Form]![Combo4]) AND ((Surgery.ORDate)>[Forms]![Report Date Form]![Text0] And (Surgery.ORDate)<[Forms]![Report Date Form]![Text2]));

Here is a portion of the data this query reports – there are actually 49 records total

Clinical Eval1
Stats PatientID SurgID TotalTime Age Total LOS ORDate
G01 BED0801 18 0:14:00 4 2 12-Aug-09
G01 BED0802 18 0:22:00 33 2 11-Aug-09
G01 BED0815 18 0:20:00 18 2 10-Aug-09
G01 BED0816 18 0:29:00 25 2 10-Aug-09
G01 BED0817 18 0:13:00 14 3 10-Aug-09
G01 BED0849 18 0:07:00 8 3 13-Aug-09
G01 BED0857 18 0:32:00 45 3 13-Aug-09
G01 BED1226 18 0:42:00 52 0 05-Aug-09
G01 BED1228 18 0:29:00 66 0 03-Aug-09

To get the total count per procedure and the average length of stay, I have the following query:

SELECT [Surgeons Name].Surgeon, [Clinical Eval1].Stats, Count([Clinical Eval1].Stats) AS CountOfStats, Avg([Clinical Eval1].[Total LOS]) AS [AvgOfTotal LOS]
FROM [Surgeons Name] INNER JOIN [Clinical Eval1] ON [Surgeons Name].[Surgeon ID] = [Clinical Eval1].[Surgeon ID]
GROUP BY [Surgeons Name].Surgeon, [Clinical Eval1].Stats;

What I’m having trouble with is getting the average time per procedure and determining the percentage of pediatric. I have tried using Totals query and average, but get an error that says the expression is typed incorrectly or is too complex to be evaluated. I have also run a query where I can get the count of those aged 18 and up and another query for ages below 18. However, I don’t know how to take those two queries and get a percentage.

I hope I have supplied enough information to explain what I’m trying to do.
Thank you in advance for your help.

 

[dhookom]

I would get rid of all formatting in the query. IMO, formatting should be reserved for the form or report controls.

Does your query really need to be a totals/group by query?

I would calculate the times in minutes in the query with an expression like:

  TotalORMinutes: DateDiff("n",[OR Start],[OR End])

Assuming you have a grouping on Surgeon on the report, if you want the average time per procedure, isn't this simply:

 =Avg([TotalORMinutes])

To get the percent that are under 18, use an expression like:

 =Sum(Abs([Age],18))/Count(*)

You might consider kicking your development up a notch by providing better names for Combo4, Text0, and Text2.

Duane
Hook'D on Access
MS Access MVP

 

[DLynnTX]

Thank you for your help, Duane. I'm sorry I'm just now getting back to you. I only get to work on this database about twice a month. I understood everything you showed me except for the percentage under 18. I'm assuming that formula would be in the report (since it doesn't work on the query) and the query I'm using already has totals of the stats, so the separate age is no longer there. I'm working on it now, and hope to figure it out. But wanted to thank you for the other information.

 

[dhookom]

To get this to work in the query, you should be able to use:

Pct18: Sum(Abs([Age],18))/Count(*)

Duane
Hook'D on Access
MS Access MVP

 

[DLynnTX]

Regardless of whether I do it in the report of the query, I get the following error:

The expression you entered has a function containing the wrong number of arguments.

 

[dhookom]

I'm having trouble seeing your expressions ;-) Can you share?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this:
Pct18: Sum(Abs([Age][!]<[/!]18))/Count(*)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Good catch PH and welcome back

Duane
Hook'D on Access
MS Access MVP

 

[DLynnTX]

Thank you both! That worked. I actually got a different error when I tried it, but realized that I had Group By turned on and it does not work with that. I'm moving forward again - thanks to the two of you.