Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Grouping Problem

Posted on 2004-09-21
Medium Priority
320 Views
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
Question by:Navicerts
• 5
• 2

LVL 27

Assisted Solution

jjafferr earned 400 total points
ID: 12116628
Hi Navicerts,

you can use the dsum with a where condition
0

LVL 16

Accepted Solution

Nestorio earned 1600 total points
ID: 12117009
You can use a sum this way in a query:

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

LVL 16

Expert Comment

ID: 12117115
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

LVL 16

Expert Comment

ID: 12119190
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

LVL 7

Author Comment

ID: 12122151
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

LVL 16

Expert Comment

ID: 12122707
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

LVL 16

Expert Comment

ID: 12160502
Hi Navicerts,

0

LVL 7

Author Comment

ID: 12160879
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month12 days, 4 hours left to enroll