Help with using function in Query

[webmagic]

I am new to all of this, so any help, direction and expertise is appreciated.
I have a query that runs and filters all kinds of information. In that query I want to run a function, but am not sure what parameters I should use. I am trying to use the orginal parameters of the function but continue to get parameter and syntax errors. Here is my function and

Public Function 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 " & 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
    Quartile = xl.WorksheetFunction.Quartile(dblData, k)
    
    
    rst.Close
    Set rst = Nothing
    Set xl = Nothing
End Function
 
Qry=
 
Quartile: [dbldata][3]

 

[FoFa]

In a query it would be basically ColumnName: Quartile("datatableString","dataString",somenumber)

Of course column names can be used instead of literals such as
ColumnName: Quartile([datatablecolumn],[datastringcolumn],[somenumbercolumn])

 

[webmagic]

Can you explain that a little bit more? Am I selecting from the original column or the column before I want to run the function? I guess I am unsure of exactly how things are running in a query. If I select the original table is the information from the other fields in the query being ran or would I get the unfiltered data? I hope this makes sense.

Thank you for your patience

 

[webmagic]

Here is what I am placing in my query and I keep getting an "undefined function" or I get The microsoft jet engine cannot find table or query named quartile.

Q1_result: Quartile("data_table","data",3)

 

[georgedwilkinson]

Here is what I am placing in my query and I keep getting an "undefined function" or I get The microsoft jet engine cannot find table or query named quartile.

Q1_result: Quartile("data_table","data",3)

So you're saying that in the table you're querying on you have a field named "data"? That's a problem right away. And you're also saying that one of the tables in your query is called "data_table"...that's unusual also.

What is the name of the table? That goes where you have the word "data_table". What is the name of the column within that table that you want the quartile from? That goes in where you have the word "data". If "data" is really the name of that column, you need a different type of help with this.

 

[webmagic]

So I can change the names

I can change the names and will try that. I can't see how that would create problems? Can you explain that? Is it the "name" itself that is the problem? I also am really wondering if the logic to this is correct. Should we query everything we want and then take the results of the query and ask for the quartile?

 

[georgedwilkinson]

I was just saying it's unusual. Data is a reserved word and really shouldn't be used as an object name. It can cause problems.

I didn't really evaluate your function as I'm not exactly sure what it's supposed to do. It's kinda funny that it takes a double as the third argument but there could be a good explanation for that (aren't the only valid values 0, 1, 2, 3, & 4?). You might want to make sure that any value you send to the double evaluates to a double (3.0 instead of 3). It's also kind of weird that you're reading in an entire recordset one row at a time and then passing that to an Excel function...seems a bit...inefficient. I'm just kinda grasping at straws with little information on the problem.

You would likely get a better answer if you pasted your SQL for this query here.

Also, have you tried this with a break point, watches, and single step in your function?

 

[webmagic]

Thank you for explaining that. I am learning. I just named those real quick to get an idea of what I was doing. I have renamed them and am trying a few things before posting again, so you can get and understanding of what I am trying to do-maybe I will get a better understanding as well.

Thank you for your time.

 

[georgedwilkinson]

Okie Doke. Let us know when you're ready and somebody'll help you.

 

[webmagic]

okay-here it goes again

I have changed my names from above and followed other suggestions and am getting the error undefined function.

I have one table with fields:

material
order_num
tank
qty
actual_qty
difference
q1_results

When I do my query i group in order of material then order_num then tank. The difference gives me the difference between qty and actual qty. I could have hundreds of numbers depending on the amount in each group. In the q1 results is where I am trying to call the function quartile.

This is where I am confused as to how this is working. Will Access make a temp group of the array in the difference column for that group that has been queried? I need the quartile to be figure based on the array and not a single number.

Also can you give me a hint as to how I should be calling my function from th query, if of course this will work.

Thank you in advanced for your time and experience.

 

[georgedwilkinson]

A couple of things:
1. can you post the SQL of your query (go to SQL view and cut/paste).
2. Are you sure that the VBA code containing Quartile() is in a module in the project?
3. It looks like you do but I want to be sure, but do you have a reference to Excel objects in your VB project?
4. What is the name of your table and which of the 7 field in it is the one you want the percentile for?
5. You realize that a percentile (aka quartile) is a group type of function, right? Even if you don't get an error, you will be comparing an aggregate to flat data.

I was able to cut and paste your code into one of my existing projects and call it successfully (I don't think it returns the right value but since I don't know what it is supposed to return, that's another issue).

You call it like this:

Quartile("TableName", "ColumnName", 1)

This will give you the 25th percentile. To get other answers, use:
2 for median
3 for 75th percentile
4 for last value
0 for first value

I'm pretty sure that you were using the wrong table name and/or column name earlier which is why I questioned you then. However, if your application cannot find the function, it is moot. You must first make the function visible to your application.

Now, I'm not sure if this even works or not...it didn't seem to work right for me. But get it running first, then we'll deal with whether it works.

 

[webmagic]

thank you for helping

Here is the sql code:

SELECT quartile_info.raw_material, quartile_info.order_num, quartile_info.tank, quartile_info.qty, quartile_info.actual_qty, quartile_info.difference
FROM quartile_info;

Yes the function is in a module and in the project

Yes, the excel objects are in the references

Can this be done. The field would be the difference column that I want to pull the array out of after it is grouped. It just doesn't seem like we can do this. Can Access create a temp table to perform a calculation on a grouping done in a query?

Your patience and time are really appreciated. Half the time I don't what to ask the other part of the time I don't know how to ask it.

 

[georgedwilkinson]

The thing is, I've solved this issue in Oracle and have a good idea how it works/should work. In my case, I was keeping test statistics on semiconductor fabricated wafers. We would test some number of die on each wafer (or even from a wafer that was part of a run) and keep certain percentiles in our data warehouse for the wafers/runs. Calculating them in Oracle was a bear and we were running into exactly the issues you are about to be running into...i.e. creating an aggregate function that doesn't behave like an out of the box aggregate function. The problem is even worse in Access because there are fewer statistical functions.

Yes, using your quartile function will return an aggregate and Access may not act like it is returning an aggregate (i.e. it will aggregate each row and insert that answer into all the rows).

If you'll supply me with the field that you want aggregated and some sample data (or the whole database) I'll try to work out a solution for you. Also, what do you expect to see?

How To Upload A Database To The Forum

 

[webmagic]

Thank you again, I hope at some point I am able to help others like you are helping me. I ahve attached the smaller test database for you to look at. I was getting the error in the query so I removed calling the function. I am looking for the first and third quartile based on the "difference" in groupings. Say a material and then the group of tanks and based on the differenec column the quartile for that group. I hope this makes sense. And again thank you sooooo much!!!

 

[webmagic]

One more thing

I must have been really tired when I wrote the last response-please excuse my spelling errors. I think the query I sent you may not have had the groupings. We group by material, source tank, then dest. tank. I apologize for not having the groupings in the file before. I am still working on trying to figure this out and really appreciate your help and experience on getting this to work.

 

[georgedwilkinson]

Somehow I missed your post with the first DB. I gotta re-familiarize myself with what you're doing. I have your file open...there were a couple of errors regarding linked tables.

Give me a little time to figure this out.

 

[webmagic]

No worries

Take as much time as you want. Everything you are doing is appreciated.

Thank you.

 

[georgedwilkinson]

OK, I took a look and have a (long) statement and a followup question.

The long statement:
Typically, when working with medians, percentiles, quartiles, etc. you are working with a set of numbers from samples over time or over a given domain.

So, in my semiconductor factory, they were making wafers with thousands of die fabricated onto them. At the end of the production line, they would put the wafer in a testing machine and test a sample (say 100) die for things like forward voltage, reverse current, X, etc.

Now, for that wafer for that particular production run, we could calculate the percentiles (let's say 25th, median, and 75th so it's like your scenario) of forward voltage, reverse current, X, etc. for a given wafer. We could expand the number of wafers we took samples from to get percentiles for an EPI run or for a fabrication run, or even from a run(s) on a certain machine during a given date range. In order to do this, we had to have 100 test records all related to the given wafer and then the wafers related to the production runs of various types. Now let's say I tested for X, in mW, and only took 5 samples from a wafer (we'll call it wafer #1). Let's say the results are:
1 mW
5 mW
8 mW
9 mW
9 mW

Remember, all of these records are from a single test run for a single wafer, wafer #1.

Now, if I push this data through the quartile() function I would expect:
quartile(mydataset, 0) = 1 (mW) - The zeroth quartile or 0 percentile.
quartile(mydataset, 2) = 8 (mW) - The 2nd quartile or median.
quartile(mydataset, 4) = 9 (mW) - The 4th quartile or 100th percentile.
quartile(mydataset, 1) = 5 (mW) - Questionable in my mind, but for theory's sake, we'll say this is accurate...The first quartile or 25th percentile.
quartile(mydataset, 3) = 9 (mW) - Also questionable...The 3rd quartile or 75th percentile.

Now, you'll notice that I had at least 5 data points that I could calculate percentile/quartile from.

The followup question:
What data in your dataset represents the sample that you are calculating percentile for? I don't see it.

And as a bonus: A followup statement:
You don't have enough data identified to do a quartile aggregation.

 

[webmagic]

I hope this helps

First again, thank you for your time.

I added some more data and have attached it. What I want to do is in a query group by material, then by source tank, then by destination tank. Within that grouping, find the different quartiles from the differences field.

Is that what you were asking?

 

[georgedwilkinson]

Really quick answer...but the problem is not resolved:
1. rename the module fnct_Quartile to "modQuartile". You have a name conflict.
2. The quartile function does not have anything to constrain it, like a where clause, thus it gives an answer over the entire recordset. I'm working on a sample for you to force a round peg into a square hole.