Multiple expression query

[mward04]

I am running a query with one expression that calculates standard deviation. I want to calculate standard error as well so I created an expression within the same query to do so. However, I now get asked to enter a parameter value for the standard deviation and the CountofAcctNum. When I just hit "enter" twice for these two without entering any data, I get the correct answer. How can I do this without having the "Enter Parameter Value" come up when I don't want it to?

Thanks!

This is my query:

SELECT HospCodes.[Hospital Name], Count(Records.AcctNum) AS CountOfAcctNum, StDev((([Triage]-[Arrival])*24*60)) AS StDevArr2Triage, Min(Records.Date) AS MinOfDate, Max(Records.Date) AS MaxOfDate, 2*[StDevArr2Triage]/([CountofAcctNum]^0.5) AS 2SE
FROM Records LEFT JOIN HospCodes ON Records.HospCode = HospCodes.HospCode
WHERE (((Records.Date) Between [Forms]![Dates]![BegDate] And [Forms]![Dates]![EndDate]) AND ((Records.Arrival) Is Not Null) AND ((Records.Triage) Is Not Null))
GROUP BY HospCodes.[Hospital Name], 2*[StDevArr2Triage]/([CountofAcctNum]^0.5)
HAVING (((StDev((([Triage]-[Arrival])*24*60)))>=-5))
ORDER BY StDev((([Triage]-[Arrival])*24*60));

 

[Golom]

Try this

SELECT H.[Hospital Name], Count(R.AcctNum) AS CountOfAcctNum, StDev((([Triage]-[Arrival])*24*60)) AS StDevArr2Triage, Min(R.Date) AS MinOfDate, Max(R.Date) AS MaxOfDate, 
2* StDev((([Triage]-[Arrival])*24*60)) /( Count(R.AcctNum) ^0.5 ) AS 2SE

FROM Records R LEFT JOIN HospCodes H ON R.HospCode = H.HospCode

WHERE (((R.Date) Between [Forms]![Dates]![BegDate] And [Forms]![Dates]![EndDate]) AND ((R.Arrival) Is Not Null) AND ((R.Triage) Is Not Null))

GROUP BY H.[Hospital Name], 
         2* StDev((([Triage]-[Arrival])*24*60)) /(Count(R.AcctNum) ^0.5 ) 

HAVING (((StDev((([Triage]-[Arrival])*24*60)))>=-5))

ORDER BY StDev((([Triage]-[Arrival])*24*60));

Access usually doesn't permit you to refer to a field alias in the computation of another field's value.

 

[mward04]

I keep getting a "Cannot have aggregate function in Group By Clause" for the 2*[StDevArr2Triage]/([CountofAcctNum]^0.5) expression. Any suggestions?

Thanks!
Mike

 

[mward04]

Golom,
I tweaked your suggestion and came up with the following. Ultimately, I understand your logic-put the functions within the expression and cut out the excess. Makes sense. Thanks for your help!
Mike

SELECT HospCodes.[Hospital Name], Count(Records.AcctNum) AS CountOfAcctNum, 2*StDev((([Triage]-[Arrival])*24*60))/(Count([Records].[AcctNum])^0.5) AS 2SE, Min(Records.Date) AS MinOfDate, Max(Records.Date) AS MaxOfDate
FROM Records LEFT JOIN HospCodes ON Records.HospCode = HospCodes.HospCode
WHERE (((Records.Date) Between [Forms]![Dates]![BegDate] And [Forms]![Dates]![EndDate]) AND ((Records.Arrival) Is Not Null) AND ((Records.Triage) Is Not Null))
GROUP BY HospCodes.[Hospital Name]
HAVING (((2*StDev((([Triage]-[Arrival])*24*60))/Count([Records].[AcctNum]))>=-5))
ORDER BY 2*StDev((([Triage]-[Arrival])*24*60))/(Count([Records].[AcctNum])^0.5);