Is it possible to create a custom aggregate function (eg.to use Slope - as in excel)?

[whitespace]

Hi..

This has had me and the team stumped for a while. Basically I need to use the excel 'slope' function (and intercept etc.) on a group of values returned in a query.

I have managed to create a function to sort of do this but I want to know if I can create a function in VBA and then use it in a query like an aggregate function - like you can use Sum/Average etc. eg. so i could have..

SELECT SUBJ05.BU_Exam_ID, SUBJ05.BU_Subject_ID, STUD05.Sex, [B]xlslope[/B]([Points_Score],[MeanSAS]) AS Expr1
FROM STUD05 INNER JOIN SUBJ05 ON STUD05.UPN = SUBJ05.UPN
WHERE (((STUD05.MeanSAS) Is Not Null))
GROUP BY SUBJ05.BU_Exam_ID, SUBJ05.BU_Subject_ID, STUD05.Sex;

I have attached the code I have so far if required. Since I need to do this on a lot of different sets of data it would be so much easier if I can just do a normal query and use the Slope function like an aggregate function. Does this make sense?

Thanks so much for any help.

-Jenny

 

[ChrisO]

This works but is slow.

Sub Test()
    
    MsgBox xlSlope(Array(2, 3, 9, 1, 8, 7, 5), Array(6, 5, 11, 7, 5, 4, 4))
    [color=green]' Returns 0.3055555555556[/color]
    
End Sub


Public Function xlSlope(ByVal vntArray1 As Variant, _
                        ByVal vntArray2 As Variant) As Double

    [color=green]'You need a reference to Excel.[/color]
    Dim objExcel As Excel.Application
    
    Set objExcel = CreateObject("Excel.Application")
    xlSlope = objExcel.Application.Slope(vntArray1, vntArray2)
    
    objExcel.Quit
    Set objExcel = Nothing
    
End Function

I don’t know about the SQL side of things but hope that gets you started.

Regards,
Chris.

 

[whitespace]

Thanks ChrisO but this seems to achieve exactly the same as what I did pretty much - I still can't use xlSlope within a query if you know what I mean.

I basically want to be able use the xlSlope function within query designer as shown in the attached picture..

This is necessary because we have a lot of different gropuing to do and basically as seen in the picture I want to calculate the slope for the set of MeanSAS and Points_Score's returned for EACH Exam Type and FOR EACH Subject within the Exam Type and then by Gender. Basically this would mean that the xlSlope function needs to be able to work like an aggregate function but I obviously get the 'You tried to execute a query that does not include the specified expression 'xlSlope.....' as part of an aggregate function.... The trouble is I NEED the xlSlope to work like an aggregate function... Am I making any sense?!!!

Thanks so much for your help with this. Maybe I didn't use your example properly?

-Jenny

 

[ChrisO]

G’day Jenny

No picture attached but it might be of more use if we could have a small demo.
It would only need to have the tables, query and a little insensitive data.

Regards,
Chris.

 

[whitespace]

I'll try and attach the picture again... (and the code as I notice that hasn't either)

Please note that I have been looking on internet for a solution to this problem aswell and it seems that oracle has an sql function REGR_SLOPE that would do the trick - is there no such thing in access?

Thanks again for any help

 

[ChrisO]

Hi Jenny.

Would it be possible for you to post a cut down version of your database with the tables, query, code, some sample data and the expected result?

It’s a bit too much for me to build from bits and pieces and I could certainly make a mistake especially since it’s 2:50AM here.

Regards,
Chris.