Quartile Calculation

[rbs]

Hi Everyone,

I have a query in access that prompts for date criteria, and then calculates min, max, average etc of this data. I am then displaying this info in a report.

I need to also calculate quartiles for this data

The Lower Quartile would be 25% up an ordered list of values, Upper quartile=75%.

I am currently pulling my hair out and need to solve this urgently.

Thanking you so much in advance.

rbs.

 

[rbs]

Sorry I wasn't very clear...

I have many records in a table with a field called type. I have an amount field for which I have calculated average and I have a count field I need the quartiles for each type of record.

Therefore if there are 9 records for one type then I need to show the 3rd and the 7th highest amounts.

I have a formula which has calculated which records I require for that type ie 3rd & 7th or respective.

But I don't know how to show the nth values.

Thanks so much for your help.

rbs.

 

[Andrew Fergus]

Hi,

You should be able to do this by ranking the records according to the amount and then using the criteria part of the query to select the 2 records you want (using your quartile calculation, I think you mentioned that you had solved this bit). If it is too complex then you could rank the amounts first and then select the 2 qaurtile records in a 2nd query.

Before you do anything, have a read of this article - it helped me with a ranking problem.

The ranking bit of your query will look something like this :

Ranking: (Select Count(*) from TableName Where [Amount] > [TableAlias].[Amount] ; ) + 1

Note : "TableAlias" is an alias for your table called "TableName" per the query statement (but use your actual table name instead) - to set the table alias, go to the query design view, click on the table in the top half of the query, Click View -> Properties -> Alias -> insert an alias for the table name here. This alias is necessary for the ranking query to work.

You can then use the criteria section of the query to match the records (or a 2nd query if it is too complex).

HTH, Andrew.

 

[rbs]

Hi,

Thanks so much for your help. I have the query returning the Ranking for each entry and a query which lists which ranking values i need.

Therfore I have 2 queries that return the following data:

Query1
Type Quartile Quartile2 Quartile3
Type1 RecordRanked2 RecordRanked4 RecordRanked6
Type2 RecrodRanked3 RecordRanked6 RecordRanked9

Query2
Record# Amount Type1 Ranked1
Record# Amount Type1 Ranked2
Record# Amount Type1 Ranked3
Record# Amount Type1 Ranked4
Record# Amount Type1 Ranked5
Record# Amount Type1 Ranked6
Record# Amount Type2 Ranked1
Record# Amount Type2 Ranked2
Record# Amount Type2 Ranked3
Record# Amount Type2 Ranked4
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked9

I then need a query that lists
Type1 AmountofType1WhereRankingis2 AmountofType1WhereRankingis4 AmountofType1WhereRankingis6
Type2 AmountofType2WhereRankingis3 AmountofType2WhereRankingis6 AmountofType2WhereRankingis9

I also have the issue that some records will be ranked the same in the query and therefore some ranking no's are not listed, ie ranking6 of type2 will not be listed if the amounts are the same.

Thanks so much for your help.

rbs.

 

[SydneyGeek]

Natalie,

Adding to Andrew's post you can get what you need using a Totals query.
In a new query based on the one you just got with Andrew's help, add the fields
Type
Rank
Rank
(ie Rank is in there twice)
Click the Totals item (sigma) in the Query toolbar. You'll see Group By under all three fields. Change the last one (the SECOND Rank) to Count.

Denis

 

[rbs]

Hi Denis,

I am confued. I am unsure how this gives me what I need.

Natalie.

 

[rbs]

Hi All,

I have solved all of my problems except for the ranking issue.

I have all amounts ranked, but in some instances the amounts are the same. In these cases there may be 2 amounts ranked 5 and the next is ranked 7. I may need the amount ranked 6.

Thanks heaps for your help!

N.

 

[rbs]

Hi All,

I have solved all of my problems except for the ranking issue.

I have all amounts ranked, but in some instances the amounts are the same. In these cases there may be 2 amounts ranked 5 and the next is ranked 7. I may need the amount ranked 6.

Thanks heaps for your help!


N.

 

[Andrew Fergus]

It sounds like you are almost there. However, if there are 2 items with a ranking of 5 and you want to select the 6th item - what do you want the query to return? Do you want both of the 5th ranked values, one of the 5th values, the 7th or none?

Andrew.

 

[SydneyGeek]

Natalie,

I am confued. I am unsure how this gives me what I need.

If you take this query...

Query2
Record# Amount Type1 Ranked1
Record# Amount Type1 Ranked2
Record# Amount Type1 Ranked3
Record# Amount Type1 Ranked4
Record# Amount Type1 Ranked5
Record# Amount Type1 Ranked6
Record# Amount Type2 Ranked1
Record# Amount Type2 Ranked2
Record# Amount Type2 Ranked3
Record# Amount Type2 Ranked4
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked5
Record# Amount Type2 Ranked9

... and take the fields that I mentioned to you, you will get the following...

Type    Rank      CountOfRank
Type1   Ranked1   1
Type1   Ranked2   1
Type1   Ranked3   1
Type1   Ranked4   1
Type1   Ranked5   1
Type1   Ranked6   1
Type2   Ranked1   1
Type2   Ranked2   1
Type2   Ranked3   1
Type2   Ranked4   1
Type2   Ranked5   4
Type2   Ranked9   1

The first 2 fields group the records by the categories that you need, the third field counts the # of records in each group.

Hope the explanation helps.

Denis