Help with using function in Query

[georgedwilkinson]

This works(ish):

SELECT quartile_info.raw_material, 
quartile_info.source_tank, 
quartile_info.dest_tank, 
Count(*) AS NumberOfSamples, 
fnct_Quartile("(select * from quartile_info where quartile_info.raw_material = '" & raw_material & "' AND quartile_info.source_tank = '" & source_tank & "' AND quartile_info.dest_tank = '" & dest_tank & "')", "qty", 2.0) AS QtyMedian
FROM quartile_info
GROUP BY quartile_info.raw_material, 
quartile_info.source_tank, 
quartile_info.dest_tank;

I don't like the way Excel calculates quartiles but that is a different issue.

It's also quite slow and the results should be kept in a DSS table to reduce the processing time.

The parentheses are required. Just replace the "qty" with whatever value in the result set you want to calculate quartile for. And replace 2.0 with whatever value represents the quartile you want to calculate for.

This along with the change of name in the module should fix you up.

 

[webmagic]

A million thank you's

I will start playing with this -a million thank you's!!!!!! I will let you know my reults later today.

 

[georgedwilkinson]

I will start playing with this -a million thank you's!!!!!! I will let you know my reults later today.

NP. Always glad to help.

 

[webmagic]

One more issue

Your program worked great! I am now putting it into our larger database and am getting an error "Syntax error in the FROM clause. I am assuming this is a sql error but when i debug it highlights my function. The RAW DATA is a query that is created when the user completes a form. I have tried several things between yesterday and today, and am unable to figure out what it wants.

I can't believe how well your program works and am dying to get it to work on ours. I can't thank you enough for helping me.

Here is the query:

SELECT [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT], Count(*) AS NumberofSamples, fnct_Quartile("(select * from RAW_DATA where RAW_DATA.RAW_MATERIAL = ' " & [RAW_MATERIAL] & " ' AND RAW_DATA.SOURCE_TANK = '" & " ' AND RAW_DATA.MANUFACTURING_EQUIPMENT = ' " & [MANUFACTURING_EQUIPMENT] & " ')","Difference",1) AS FQ
FROM AS RAW_DATA
GROUP BY [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT];

Here is the function:

Public Function fnct_Quartile(data_table As String, data As String, k As Double) As Double
Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
rst.Open "Select * from " & RAW_DATA, CurrentProject.Connection, adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(data)
rst.MoveNext
Next x
fnct_Quartile = xl.WorksheetFunction.Quartile(dblData, k)


rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

 

[georgedwilkinson]

Something's missing:

SELECT [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT], Count(*) AS NumberofSamples, fnct_Quartile("(select * from RAW_DATA where RAW_DATA.RAW_MATERIAL = ' " & [RAW_MATERIAL] & " ' AND RAW_DATA.SOURCE_TANK = '" & " ' AND RAW_DATA.MANUFACTURING_EQUIPMENT = ' " & [MANUFACTURING_EQUIPMENT] & " ')","Difference",1) AS FQ
FROM AS RAW_DATA
GROUP BY [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT];

What does SOURCE_TANK need to equal to meet the where condition? There is no value there.

You put a space between the apostrophe and the value in the where clause...that won't work.

What table is the data coming from? You need to include the table name in the from clause or remove the "AS" if the table name is RAW_DATA (which I assume is the case).

Also, it looks like you changed the quartile function from what it had been. Why? Now it won't aggregate and cannot accept a where clause.

 

[webmagic]

I didn't think I changed anything on my function, I had copied the function that I sent you. I will go and check again. here is the new query.


SELECT [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT], Count(*) AS NumberofSamples, fnct_Quartile("(select * from RAW_DATA where RAW_DATA.RAW_MATERIAL = '" & [RAW_MATERIAL] & "' AND RAW_DATA.SOURCE_TANK = '" & "' AND RAW_DATA.MANUFACTURING_EQUIPMENT = '" & [MANUFACTURING_EQUIPMENT] & "')","Difference",1) AS FQ
FROM RAW_DATA
GROUP BY [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT];

 

[georgedwilkinson]

SELECT [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT], Count(*) AS NumberofSamples, fnct_Quartile("(select * from RAW_DATA where RAW_DATA.RAW_MATERIAL = '" & [RAW_MATERIAL] & "' AND RAW_DATA.SOURCE_TANK = '" & "' AND RAW_DATA.MANUFACTURING_EQUIPMENT = '" & [MANUFACTURING_EQUIPMENT] & "')","Difference",1) AS FQ
FROM RAW_DATA
GROUP BY [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT];

Still no value for SOURCE_TANK. This will get you no results.

Here's what I got from you (plus my troubleshooting stuff and preferred formatting):

Public Function fnct_Quartile(data_table As String, data As String, k As Double) As Double
Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
    Set xl = CreateObject("Excel.Application")
    Set rst = New ADODB.Recordset
    Debug.Print data_table 'Take this out after you've got it working right.
    rst.Open "Select * from "[COLOR=red][B] & data_table,[/B][/COLOR] CurrentProject.Connection, adOpenStatic
    ReDim dblData(rst.RecordCount - 1)
    For x = 0 To (rst.RecordCount - 1)
        dblData(x) = rst(data)
        rst.MoveNext
    Next x
    fnct_Quartile = xl.WorksheetFunction.Quartile(dblData, k)
    
    'fnct_Quartile = 1
    rst.Close
    Set rst = Nothing
    Set xl = Nothing
End Function

The part in red changed.

 

[webmagic]

I didn't save on the last query. I am now getting an error for: No Value given for one or more required parameters. This might be coming in from the main form. I just wanted to get back to you to see if all this looks right.

Thank you so much is I haven't said that a hundred times today.

SELECT [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT], Count(*) AS NumberofSamples, fnct_Quartile("(select * from RAW_DATA where RAW_DATA.RAW_MATERIAL = '" & [RAW_MATERIAL] & "' AND RAW_DATA.SOURCE_TANK = '" & [SOURCE_TANK] & "' AND RAW_DATA.MANUFACTURING_EQUIPMENT = '" & [MANUFACTURING_EQUIPMENT] & "')","Difference",1) AS FQ
FROM RAW_DATA
GROUP BY [RAW_DATA].[RAW_MATERIAL], [RAW_DATA].[SOURCE_TANK], [RAW_DATA].[MANUFACTURING_EQUIPMENT];


Public Function fnct_Quartile(data_table As String, data As String, k As Double) As Double
Dim rst As ADODB.Recordset
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
Debug.Print data_table 'take this out later
rst.Open "Select * from " & data_table, CurrentProject.Connection, adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(data)
rst.MoveNext
Next x
fnct_Quartile = xl.WorksheetFunction.Quartile(dblData, k)


rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

 

[georgedwilkinson]

This certainly looks ok as a stand alone query (which is, by the way, how you should test until it is solid). Not sure about your error when using it with a form.

 

[webmagic]

Okay-I will keep working and let you know what I find-Again - a million thank you's!

 

[webmagic]

I am attaching the newest attempt. I am trying everything and am not sure how to fix the newest error. I did get a smaller version working. Then created a make table from a query which I thought would work, but getting new errors. Any suggestions will be appreciated. I am at my wits end, and completely exhausted but far from giving up.

Thank you again!

 

[georgedwilkinson]

Two problems:
You're trying to take the quartile of column "difference", which does not exist in either of the source tables.

Also, there is no check in your quartile function for record sets with 0 records. Thus, if you send a table/query to the function with zero records, you will get an error.

 

[georgedwilkinson]

Sorry, my last post was confused. The column "difference" in the true_value table is null.

You still need to address the 0 recordset issue, too.

 

[webmagic]

Good morning,
I thought by using a not null statement in the query I was alleviating the possible null error in calculations when the query made the table. When I try to run a query now, I get an invalid use of null. I made a few other slight changes and am once again lost.

Thank you again for taking this time.

 

[georgedwilkinson]

I don't see the "not null" you're referring to. Are you talking about in dq2_test?

 

[webmagic]

I was working and zipped and it didn't save my latest. Very sorry. Here is the latest. Still getting the error.I made a few other changes, but didn't help.

 

[georgedwilkinson]

Still don't understand the problem. You created a new query that uses not null that seems to work (not sure what I'm looking at) but it doesn't calculate any percentiles.

 

[webmagic]

I guess neither do I. I run the query to create true_data. Then I run the dq2_test to run the function and that is where I get the error "invalid use of null". and when I debug it takes me into the function. I am still looking as to why-so any suggestions will be great. I am not figuring any quartiles -so?????????

Thank you very much

 

[georgedwilkinson]

OK, I "tweaked" your quartile function. Use this:

Public Function fnct_Quartile(data_table As String, data As String, k As Double) As Double
    Dim rst As ADODB.Recordset
    Dim dblData() As Double
    Dim xl As Object
    Dim x As Integer
    Dim DebugFlag As Boolean
    DebugFlag = True
    
    On Error GoTo fnct_QuartileErrorHandler
        Set xl = CreateObject("Excel.Application")
        Set rst = New ADODB.Recordset
        If DebugFlag Then
            Debug.Print data_table 'take this out later
        End If
        rst.Open "Select * from " & data_table, CurrentProject.Connection, adOpenStatic
        ReDim dblData(rst.RecordCount - 1)
        For x = 0 To (rst.RecordCount - 1)
            If rst(data) & "" <> "" Then
                dblData(x) = rst(data)
            End If
            DoEvents
            rst.MoveNext
        Next x
        fnct_Quartile = xl.WorksheetFunction.Quartile(dblData, k)
        DoEvents
        
        rst.Close
        Set rst = Nothing
        Set xl = Nothing
        Exit Function
fnct_QuartileErrorHandler:
    
    If DebugFlag Then
        Debug.Print Err & vbCrLf & Error
    Else
        MsgBox Err & vbCrLf & Error, , "Error in Function fnct_Quartile"
    End If
    Resume Next
    
End Function

Be sure to check the immediate window and/or use break points in your code until you get this fixed.

Now, on to the question of why there are errors. You are asking the function to give you a quartile of the aggregated column "Difference" which has no data in it. You cannot take an aggregate of something that is null. Are you sure you need the quartile for "Difference"? And if so, can't "Difference" just be calculated, like this:

QUANTITY - ACTUAL_QUANTITY

wouldn't you, for the sake of this project, just set the Difference field to that formula in an update query?

 

[webmagic]

Thank you so much-We finally got it working just perfectly. I cannot thank you enough for all of your time and patience. I sure did learn a lot! I hope other people did too!

I do have one glitch when we add this to the larger database. When we run the quartile function, it freezes, when we start it again, it has erased the code in the quartile function and only leaves the top two lines. We are testing but didn't know if this was a bug in access2000, or if this is conflicting with another module. Just thought I would throw it out there.


Thanks again for all of your help-I could have kissed you on your forehead Friday!!!!!