Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Grouping Problem

Hello Experts,

Today just isn't my day!  I am trying to figure out how I can find the deviation from the linear regression through the use of custom made functions.

So far i have this...

___________________________________________________________________________
Option Compare Database

Function LinRegSlope(n, x, y, xy, xx)

'n = Count of items
'x = Sum of the X Axis
'y = Sum of the Y Axis
'xy = Sum of the X Axis * the Y Axis  SUM(x*y)
'xx = x^2

LinRegSlope = ((n * (xy)) - ((x) * (y))) / ((n * (xx)) - ((x) * (x)))     'The slope of the line

End Function

Function LinRegYInt(n, x, y, xy, xx)


'n = Count of items
'x = Sum of the X Axis
'y = Sum of the Y Axis
'xy = Sum of the X Axis * the Y Axis  SUM(x*y)
'xx = x^2


Dim m As Double ' Slope

m = ((n * (xy)) - ((x) * (y))) / ((n * (xx)) - ((x) * (x)))     'm = The slope of the line

LinRegYInt = (y - (m * x)) / n

End Function
________________________________________________________________________

This is working fine but my problem is the grouping.  Is there a way i can use the SUM and at the same time find the deviation from an indivual record?

Thanks!
0
Navicerts
Asked:
Navicerts
  • 5
  • 2
2 Solutions
 
jjafferrCommented:
Hi Navicerts,


you can use the dsum with a where condition
0
 
NestorioCommented:
You can use a sum this way in a query:

Select fld1, (select sum(amount1) from table1) as SumAmount
From table1
0
 
NestorioCommented:
Example using a function:

Select LinRegSlope((select count(*) from table), (select sum(x) from table), (select sum(y) from table), (select sum(x*y) from table), (select sum(x*x) from table)) as m
From table

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NestorioCommented:
Try this:

SELECT x, y, y - (q.m*x+ q.b) AS Deviation
FROM tablexy, [select
        count(*) as n,
        sum(x) as sx,
        sum(y) as sy,
        sum(x*x) as sxx,
        sum(x*y) as sxy,
        ((n * (sxy)) - ((sx) * (sy))) / ((n * (sxx)) - ((sx) * (sx)))  as m,
        (sy - (m * sx)) / n as b  from tablexy]. AS q;

where "x" and "y" are the fields x and y of the table "tablexy"
0
 
NavicertsAuthor Commented:
I stopped working on it about an hour after i made the post yesterday, sorry for the delay :)  I'll test this out today and let you guys know how it works out, thank you for all the suggestions!
0
 
NestorioCommented:
Hi Navicerts,

I've tested  the last solution I posted and it worked OK. Just one query and no need to use functions.

Regards
0
 
NestorioCommented:
Hi Navicerts,

Any updates on this?
0
 
NavicertsAuthor Commented:
oops! forgot to come back to this one, i tried out both options and they work.  I am going to go with making the functions because my end users like to do thier own sql statements and use access so i figure ill give them a function they can use.  Unfortunatly i have been sidetracked before i could finish the project (not a surprise here).  But thank you for the soultions!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now