Fancy Math

1. Senior Member
Join Date
Dec 1969
Posts
406

## Fancy Math

Alright, not that fancy, but everything seems more intimidating when you don&#039;t know where to start...<BR><BR>Anyways, I&#039;m a long time newbie :) working with an Access/ASP and I&#039;ve gotten about as complex with my math functions as getting Standard error:<BR>strQSE = "STDEVP(Data.zinc_tot)/(sqr(count(Data.zinc_tot))) as SEzinc_tot ",<BR><BR>but now I need to do something more complex - get the geometric mean. <BR><BR>The geometric mean is the nth root of (xi*xii*...xn) or also (xi*xii*...xn)to the power of 1/n.<BR><BR>Essentially I need to multiply all (non-null!) values within a field together and then either 1)take the nth root of the total or 2)apply the power of 1/n to the total.<BR><BR>Can comeone give me some direction?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## It has to be in SQL????

It&#039;s trivial in VBS:<BR><BR>&#060;%<BR>count = 0<BR>product = 1<BR>Do Until RS.EOF<BR> count = count + 1<BR> product = product * RS("x")<BR> ...<BR> RS.MoveNext<BR>Loop<BR>geoMean = product ^ ( 1.0 / count )<BR>%&#062;<BR><BR>But doing that *inside* SQL???<BR><BR>I don&#039;t know how to get SQL to do "product of field". We could do it in SQL Server, using a stored proc, but...<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
406

## RE: It has to be in SQL????

Hey Bill,<BR>Thanks - that might work. You know, I&#039;m not smart enough to know whether it needs to be in SQL or not. Is there a handy reference that might tell me when I need SQL and when VB will do? I seem to use them interchangably... (probably a bad thing)

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## SQL Ref for Access:

http://msdn.microsoft.com/library/en-us/office97/html/output/F1/D2/S5A318.asp<BR><BR>*USUALLY*, anything you can do in SQL is better done there. But is there a hard and fast rule? Not that I&#039;m aware of.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
406

## RE: SQL Ref for Access:

Bill,<BR><BR>Do you ever sit around at home, drink beer and think about how helpful you are to all of us ASP-boneheads? You should. I may just raise a glass to you this eve...

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Sigh...I can't...

...drink beer, that is. I had half a pint up in Canada 3 weeks ago and my blood sugars shot right through the roof. Alcohol is murder on diabetics.<BR><BR>Anyway, if I was gonna drink, I&#039;d drink Bailey&#039;s Irish Cream.<BR><BR>What you might not be aware of is the 200 or so handy "favorites" I have tucked away and reasonably well organized, so when a question comes along it&#039;s just a few clicks to the reference material. I&#039;m one person who really does believe in RTFM&#039;ing.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•