call function in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anil - 11 May 2006 23:59 GMT

hi all
I need to design the query that uses the query fields as input in
formula and calculates result.
e.g FIELDS ARE
[PARAMETER] [MEANResult] [STDEV] [NOOFSAMPLES] [FUNCTION(X) ]

Mean,stdev and noof samples are calculated as aggregate function.

Now i need to calculate X value as
mean+{(stdev*t(a))/sqrt(noofsamples)}
where ta is value got from fixed table comparing with noofsamples.eg if
noofsamples is 20 then ta=2.2 etc.
IS it possible to do it in query as it has to call all fields in query
and then calculate function value.
thanks
anil

Reply to this Message

arthurjr07@gmail.com - 12 May 2006 02:42 GMT

here is the SQL statement.

SELECT PARAMETER,
Format(Avg([FieldToAve]),"Standard") AS MeanResult,
Format(((StDev(FieldToSdev)),"Standard") AS SDevResult,
NoofSamples,
(MeanResult+(((SDevResult*t(NoofSamples))/NoofSamples)) as X
FROM Table
Group By FieldToGroup

********************************************************************
and put this t(a) function in the module

Function t(a as integer)

Dim rs As New ADODB.Recordset
rs.Open "Select * From RemoteMaintnance where RemoteID = " & s,
CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
X = rs!Max_Flow
Else
X = 0
End If
rs.Close
Set rs = Nothing

End Function

Be sure to supply all necessary data like the table name, fieldname

HTH

Reply to this Message

arthurjr07@gmail.com - 12 May 2006 02:46 GMT

Used this function instead.

Function t(a as integer)
Dim rs As New ADODB.Recordset
rs.Open "Select taFieldName From FixedTableName where
NoofSampleFieldName = " & a,
CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
X = rs!taFieldName
Else
X = 0
End If
rs.Close
Set rs = Nothing
End Function

Replace taFieldName, FixedTableName
and the NoofSampleFieldName

Reply to this Message

anil - 12 May 2006 04:43 GMT

thanks a lot.
U solved my big problem
it worked well and as well as I wanted.
thanks
anil

Reply to this Message