# 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!
LVL 7
###### Who is Participating?

Commented:
You can use a sum this way in a query:

Select fld1, (select sum(amount1) from table1) as SumAmount
From table1
0

Commented:
Hi Navicerts,

you can use the dsum with a where condition
0

Commented:
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

Commented:
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

Author 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

Commented:
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

Commented:
Hi Navicerts,

Any updates on this?
0

Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.