85th Percentile of a set of records

[Preacha]

Look at the PERCENTILE function in excel, its like that. I want it to have the percentile for each group of dates, ie. the percentile for 24/06/04, the percentile for 25/06/04, etc

 

[WayneRyan]

Preacha,

Don't want to look at Excel now. If you take my last query and then
run this after it, I think that's what you want. Maybe there's a way
to do it in one query, but for the moment I'm done.

SELECT Query4.TheDate, Min(Query4.Speed) AS MinOfSpeed
FROM    Query4
GROUP BY Query4.TheDate;

See ya,
Wayne

 

[WayneRyan]

Preacha,

Actually in the first query, you want Top 15 Percent. Then you take the
min of that.

OK, now I'm done.

Wayne

 

[Preacha]

I don't think that is how percentiles are calculated, but thanks anyway

 

[Rich]

http://www.fabalou.com/Access/Modules/percentile.asp

 

[Preacha]

Yeah, if you look on the previous page you'll see that I posted up code from that very page for the percentile function. I need to use that function to create a new table with Dates and their corresponding Percentiles. I was thinking something like this . . ..

the createPercentileTable() will do the creation of the percentile table

Function createSpeedDateTable(edate As String)
'Creates a table with all speeds, for the purposes of calculating 85th Percentile
On Error GoTo close_sub
sqlSngSpdTbl = "SELECT SpeedImport.RecordDate, SpeedImport.Speed INTO [" & edate & "Table] " & _
"FROM SpeedImport " & _
"WHERE (((SpeedImport.RecordDate) = #" & edate & "#)) " & _
"ORDER BY SpeedImport.RecordDate;"
DoCmd.RunSQL (sqlSngSpdTbl)
close_sub:
Exit Function
End Function

Function createPercentileTable()
sql1 = "SELECT * " & _
         "FROM Percentile "
sql2 = "SELECT * " & _
        "FROM Dates"
     
    Dim inputtable As String          
    Set dbs = CurrentDb
    Set rst1 = dbs.OpenRecordset(sql1)
    Set rst2 = dbs.OpenRecordset(sql2)

    #### Loop til end of the date table or until last date or something ??? 
   ### maybe while not rst2.EOF orsomething????
    rst1.AddNew
    rst1!RecordDate = rst2!RecordDate ' should have record1 from rst1 = rst2 etc
    spdDate = createSpeedDateTable(rst2!RecordDate(1 to end of record))
    inputtable = CStr(rst2!RecordDate (1 to end of record))& "Table")
    rst1!Percentile = PercentileRst(inputtable, "Speed", 0.85)
    rst1.Update
    rst1.AddNew

     Set rst1 = Nothing
End Function

where the PercentileRst function is the code from that web page.

The function will create a table for each date and use each individual table to create the percentile field for a corresponding date. or if you guys think there is a better way (???)

How would you loop through the Date table until it is the end of the record? or should I have an array of dates that I should use instead of the dates table? The dates table just contains a list of the dates available (with no repeat dates ie. 24/6/04, 25/6/04, 27/6/04, 28/6/04, etc

Also, do you guys know howto have queries run automatically and not have the dialog box pop up prompting the user to select OK for the query to run, because I want a function to run multiple queries in the background without the user knowing. This might cause a problem, so I would also like to make sure user can't execute the query more than once...

I hope all this makes sense, thanks guys heaps!!!!

 

[Preacha]

I found out howto do it guys just

    rst2.MoveFirst
    While Not rst2.EOF And Not rst2.BOF
    rst1.AddNew
    rst1!RecordDate = rst2!RecordDate
    spdDate = createSpeedDateTable(rst2!RecordDate)
    inputtable = CStr(rst2!RecordDate & "Table")
    rst1!Percentile = PercentileRst(inputtable, "Speed", 0.85)
    rst1.Update
    rst1.AddNew
    rst2.MoveNext
    Wend

Easy I know
still gotta know howto do the queries thing. I want the built in queries that I hard-coded into VBA - to run by themselves, once the users input(Without the user clicking yes or no)
thanks

 

[Preacha]

Never mind I found out

DoCmd.SetWarnings False

Now, how would I check that a record contains a specific value before I import?

 

[Jon K]

sqlSngSpdTbl = "SELECT SpeedImport.RecordDate, SpeedImport.Speed INTO [" & edate & "Table] " & _
"FROM SpeedImport " & _
"WHERE (((SpeedImport.RecordDate) = #" & edate & "#)) " & _
"ORDER BY SpeedImport.RecordDate;"

DoCmd.RunSQL (sqlSngSpdTbl)

RecordDate 85th Percentile
24/06/2004 60.7
25/06/2004 55
26/06/2004 55.7

It seems your system uses UK date settings but you happen to use the # sign (which defaults to US date format) to delimit dates.

Take a look at Pat's thread about correctly delimiting UK dates in code:
http://www.access-programmers.co.uk/forums/showthread.php?t=63221

.

 

[Preacha]

Ok, cheers

 

[DLB]

Though Access doesn't have a Percentile function, if you make a reference to the Microsoft Excel X.X Object Library (when the code window is open, choose menu Tools, References... and select the library), you can then use the Excel function in Access VBA by passing an array of figures to it, e.g.

sngPercentile = WorksheetFunction.Percentile(arraySpeed, 0.85)

Jon,

I wanted to try it out, as it is a good way of directly using Excel functions in Access. But I'm no good at arrays.

Is there a better way to pass values to an array than the following when there are many records e.g. 100:

Dim arraySpeed(100)

For i=0 to 99
   arraySpeed(i) = rstSpeed!Speed
   rstSpeed.MoveNext
Next i

Thanks

 

[Jon K]

You can use the GetRows method of the recordset to pass all the values to an array.

I have attached a database using Preacha's sample data as illustration. You can open the form and click on the command button to update the percentile table with the daily percentiles.

In the code, I used:

Dim arraySpeeds
..............
..............
arraySpeeds = rstSpeeds.GetRows(rstSpeeds.RecordCount)

The code was written in DAO.

 

[Preacha]

If I were to specify that particular hours should be listed, I just add that in the SQL code right? I think your idea is much quicker and simpler than my idea.
Anyway, do you guys know how I would Go about checking if particular values exist in the table before importing them? This is for a speed counter database. For instance, if I were to import the speed values one day and import the same file with more record entries (That have been acumulated over the day). If anyone doesn't understand me let me know.
Anyway guys, this is the database I'm working on atm. I want to be able to import multiple files as well and make sure that it groups each location (description and direction) in the report table (Hopefully you will understand when you see it)
I've attached the files that I'm importing, 173.TBL and 105.tbl are speed tbl files and 261.tbl is a classifcation file, try importing them and you will see. As you will see, they are rather large files and take a while to import (like 3-4 mins) So Don't worry if access seems to crash. Also I want to put a progress bar on it when it imports, any ideas? Btw, you need at least access XP to run it and/or have the access control libraries 10 enabled, etc (for the select file dialog box ). Also, it doesn't seem to read til the end of the file (unless you put a line in). I have tried putting in "UNTIL <> Date" but doesn't work that well.

I'm not sure how this database will turn out in the long run., I'm unsure that the relationships are the best structure and how I am reading and outputting some of the data. Oh btw you need to click "Update Report tables" on the main importing form to produce the queries and tables required for the reports to be produced properly. Oh and do you guys know how I could produce a new report based on and exist report (but pointed to a new table, for instance if you were to specify a particular interval??). There are many issues I can raise with this database, anyway . . . . .

The link to my files is here http://www.cs.newcastle.edu.au/~c3003364/trafficCountersClass.zip
(because they won't let a guy upload a measly 240k)


Hehe, I hope you get all this
cheers guys

 

[DLB]

Jon,

Thanks for the sample database. Never thought it was that simple and easy.

Really appreciated it. Thanks again.

 

[Preacha]

Oh yeah and thanks

 

[Preacha]

Can someone else test that and see if it works, how would I implement it using the above excel example? Does anyone know howto speed up the process of reading files?

 

[sseshadr]

Need some guidance on using your code for calculating percentile.

Jon,

I am new to the forum and new to Access. I have to build a query to find the 90th percentile (similar to the example that you provided). Would you be able to share the steps required to set this up for a new table in Access?

Thanks,
Srikanth

You can use the GetRows method of the recordset to pass all the values to an array.

I have attached a database using Preacha's sample data as illustration. You can open the form and click on the command button to update the percentile table with the daily percentiles.

In the code, I used:

Dim arraySpeeds
..............
..............
arraySpeeds = rstSpeeds.GetRows(rstSpeeds.RecordCount)

The code was written in DAO.