If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
excel calculations in Access
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 |
#3
|
|||
|
|||
excel calculations in Access
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 |
#4
|
|||
|
|||
excel calculations in Access
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 |
#5
|
|||
|
|||
excel calculations in Access
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 |
#6
|
|||
|
|||
excel calculations in Access
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 . |
#7
|
|||
|
|||
excel calculations in Access
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 |
#8
|
|||
|
|||
excel calculations in Access
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 . |
Thread Tools | |
Display Modes | |
|
|