excel calculations in Access

Hi,

I have a complex query written in Excel which i want to replicate in Access,
however when i do it flags a error message saying undefined functions. So i'm
looking for the eqivualents in Access. The ones it can't find a

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks

SQRT(x) is x ^0.5

Inverse and Chi (square) distribution and Normal (0,1) distribution ? FMS
inc. was having a statistical package few years ago (and probably still have
one),


You can Bing (or Google) and find some interesting algorithm, such as
http://home.online.no/~pjacklam/notes/invnorm/ or Wikipedia (for Inverse of
Chi square, solution based on Gamma function, which again, can be found in
many places, such as at http://www.rskey.org/gamma.htm)





Vanderghast, Access MVP



"sdg8481" wrote in message
...
Hi,

I have a complex query written in Excel which i want to replicate in
Access,
however when i do it flags a error message saying undefined functions. So
i'm
looking for the eqivualents in Access. The ones it can't find a

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with
Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with
Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks

Hi,

Thank you for your reply, but unfortunatly i'm not sure i understand. I'm
looking to build the ChiINV and Normsinv into my access query. you mentioned
a statistical package where can i find this, and that first link appears to
be broken.

Thanks thou

"vanderghast" wrote:

SQRT(x) is x ^0.5

Inverse and Chi (square) distribution and Normal (0,1) distribution ? FMS
inc. was having a statistical package few years ago (and probably still have
one),


You can Bing (or Google) and find some interesting algorithm, such as
http://home.online.no/~pjacklam/notes/invnorm/ or Wikipedia (for Inverse of
Chi square, solution based on Gamma function, which again, can be found in
many places, such as at http://www.rskey.org/gamma.htm)





Vanderghast, Access MVP



"sdg8481" wrote in message
...
Hi,

I have a complex query written in Excel which i want to replicate in
Access,
however when i do it flags a error message saying undefined functions. So
i'm
looking for the eqivualents in Access. The ones it can't find a

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with
Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with
Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks

If you return a reference to the Microsoft Excel object Library (Tools |
References on the VBA menu bar), this will expose the Excel worksheet
functions within the Access application. You can then write functions in
Access to call them, so the Access functions can then be called in the query.
I've tried creating the following functions, and they seem to work fine:


Public Function NORMSINV(dblProbability As Double) As Double

NORMSINV = Excel.WorksheetFunction.NORMSINV(dblProbability)

End Function


Public Function CHIINV(dblProbability, intDegrees_Freedom As Integer) As
Double

CHIINV = Excel.WorksheetFunction.CHIINV(dblProbability,
intDegrees_Freedom)

End Function


Ken Sheridan
Stafford, England

sdg8481 wrote:
Hi,

I have a complex query written in Excel which i want to replicate in Access,
however when i do it flags a error message saying undefined functions. So i'm
looking for the eqivualents in Access. The ones it can't find a

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks

--
Message posted via http://www.accessmonster.com

PS: the body text of each of the functions is a single line. Your
newsreader is likely to have split the last one at least over two lines.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Thank you Ken, this sounds like it will do the trick....However, i'm sorry
for being dumb, but how do i actually call these functions in query
expression, so that each row (set of data) returns its true value.

Thank you, and as i said sorry if this is a dumb question

"KenSheridan via AccessMonster.com" wrote:

PS: the body text of each of the functions is a single line. Your
newsreader is likely to have split the last one at least over two lines.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.

Having created the reference to the Excel object library, you then paste the
two functions into any standard module in the database. Just open any
standard module, either an existing one or a new one, and paste the code from
my first reply below the two existing lines in the modules 'declarations'
area, not forgetting to remove any unwanted line breaks which your newsreader
might have inserted. They'll then be inserted as functions which will
available anywhere in the database.

In the query you call them in the same way as any built in Access function.
Your expression is pretty complex, so I'll have to assume that its correctly
computing the values of the arguments for the two functions, but as I've
called them by the same names as the Excel functions all you should need to
do with it is change the reference to the Excel SQRT function to the
equivalent Access SQR function.

Ken Sheridan
Stafford, England

sdg8481 wrote:
Thank you Ken, this sounds like it will do the trick....However, i'm sorry
for being dumb, but how do i actually call these functions in query
expression, so that each row (set of data) returns its true value.

Thank you, and as i said sorry if this is a dumb question

PS: the body text of each of the functions is a single line. Your
newsreader is likely to have split the last one at least over two lines.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com

Ken you are an absolute legend.....Thank you very much

"KenSheridan via AccessMonster.com" wrote:

Having created the reference to the Excel object library, you then paste the
two functions into any standard module in the database. Just open any
standard module, either an existing one or a new one, and paste the code from
my first reply below the two existing lines in the modules 'declarations'
area, not forgetting to remove any unwanted line breaks which your newsreader
might have inserted. They'll then be inserted as functions which will
available anywhere in the database.

In the query you call them in the same way as any built in Access function.
Your expression is pretty complex, so I'll have to assume that its correctly
computing the values of the arguments for the two functions, but as I've
called them by the same names as the Excel functions all you should need to
do with it is change the reference to the Excel SQRT function to the
equivalent Access SQR function.

Ken Sheridan
Stafford, England

sdg8481 wrote:
Thank you Ken, this sounds like it will do the trick....However, i'm sorry
for being dumb, but how do i actually call these functions in query
expression, so that each row (set of data) returns its true value.

Thank you, and as i said sorry if this is a dumb question

PS: the body text of each of the functions is a single line. Your
newsreader is likely to have split the last one at least over two lines.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com

.