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]

Hi Andrew,

Thanks for your help.

I have achieved nearly all that I need by assigning the rankings, and then running a query that performs decides if the record number of the first quartile value is equal to the ranking then it returns the value otherwise it returns 0. The same for quartile 2 & 3.

Then I have another query which returns the max of these fields.

If there are 2 values for ranking 5 and no value for ranking 6 and I need ranking 6 to be returned for the quartile2 field, currently there will be a zero value in the quartile2. I need it to return the value of the record ranked 5 (only once).

Thanks in advance,


Natalie.

 

[Andrew Fergus]

Hi Natalie.

If there are 2 records ranked 5 and you want the 6th ranked record, I understand that you want the 5th ranked record instead - but which one? Is there some way of picking one of these items that best represents the 6th ranking or does it not matter given they both have the same value?

Assuming either record is ok, can you modify your query to return the ranking record -1 instead of zero? What code have you used to select the 6th record in the query? Would it be possible to make the suggested adjustment?

However, the problem with this method is if there are 3 records ranked 4th then there are no records ranked 5th or 6th - you see the problem. The other problem with this method is that it might inadvertently return 2 records (the 2 items ranked 5th) instead of the 1 record. A workaround might involve the use of the "Group By / Count" methods suggested by Denis.

Keep us posted.

HTH, Andrew.

 

[rbs]

Hi Andrew,

This does not achieve what I need. I have produced the following expression in my query, to get the value, but it is coming up with an error message.

Quartile1: Max((DMin("Abs([ranking] - "&{Round((1/4*[REPORT_STATE_COUNT].[ncount]-1 )+1))&")"Wage_Record Query_State")=Round(1/4*([REPORT_STATE_COUNT].[ncount]-1)+1)))

This is coming back with the error message;

"the expression you entered as a query parameter produced this error: 'database can't find the name 'ENTER DATE:' you entered in the expression'"

The wage record query is asking for parameter values for the DATE & STATE fields.

Can someone please help!!!

Thanks so much in advance.