Quartiles and Outliers

[gtg430i]

Hello - I am new to this forum. I hope I am posting in the right place.

I need help in eliminating outliers from a set of data. I started with creating a function to get the quartiles. The function is below (This code was originally written by Michael Walsh):
' Code courtesy of
' Michel Walsh
'
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function
' ***************Code start**************


And this is few rows of my access table:

Car Year	Make	Model	Subseries	Color	Drs	Price
2010	INFINITI	G37 4X2	S	WHITE	2	$10,600.00
2010	INFINITI	G37 4X2	BASE	LIQUID P	2	$10,800.00
2010	INFINITI	G37 4X2	BASE	LIQUID P	2	$12,800.00
2010	INFINITI	G37 4X2	JOURNEY	BLACK OB	2	$15,500.00
2010	INFINITI	G37 4X2	JOURNEY	BLACK OB	2	$15,800.00
2010	INFINITI	G37 4X2		GRAPHITE	2	$16,000.00
2010	INFINITI	G37 4X2	JOURNEY	BLUE SLA	2	$17,000.00
2010	INFINITI	G37 4X2		GREY	2	$17,300.00

I need to remove the outliers from the price column. First I keep getting an error with getting the lowest 25% records of the data. This is how I am entering the function:

Car Year	Make	Model	Quartile: Xpercentile([Previous Sales]![Price],[Previous Sales],0.25)
Previous Table	Previous Table	Previous Table
Group By	Group By	Group By	Expression
checked	checked	checked	checked

It keeps returning" you tried to execute a query....... as part of an aggregate function"

Could you please look into it?

Thank You,

[June7]

You might find this of interest https://www.accessforums.net/access/...74/index3.html

[ssanfu]

Quartile: Xpercentile([Previous Sales]![Price],[Previous Sales],0.25)

Note that the first two arguments of the function are STRING parameters and need quote delimiters.

Try

Quartile: Xpercentile("Price","[Previous Sales]",0.25)

The SQL of the query:

SELECT [Previous Sales].[Car Year], [Previous Sales].Make, [Previous Sales].Model, Xpercentile("Price","[Previous Sales]",0.25) AS Quartile
FROM [Previous Sales]
GROUP BY [Previous Sales].[Car Year], [Previous Sales].Make, [Previous Sales].Model;

BTW, you shouldn't use spaces in object names (fields, tables, queries, modules, reports)....

[gtg430i]

Thank you very much it worked!!

Could you please explain what do you mean by not including spaces? in an example please.

Thanks

[gtg430i]

Hi again - One more question. I calculated correctly my 25%, 50%, 75% quartiles. Also I calculated a lower and an upper limit. Now I need to calculate the average of prices that fall ONLY between the lower and upper limits. I am not sure where my mistake is, but when I click run, 2 variable boxes pop out one for the lower and one for the upper asking me to enter values for the two boxes. However the LL and UL where previously calculated.

This is the SQL code:

SELECT [Previous Sales].[Car Year], [Previous Sales].Make, [Previous Sales].Model, Xpercentile("Price","[Previous Sales]",0.25) AS Quartile1, Xpercentile("Price","[Previous Sales]",0.5) AS Quartile2, Xpercentile("Price","[Previous Sales]",0.75) AS Quartile3, [Quartile3]-[Quartile1] AS IQL, [Quartile1]-1.5*[IQL] AS [Lower Fence], [Quartile3]+1.5*[IQL] AS [Upper Fence], Avg([Previous Sales].Price) AS AvgOfPrice
FROM [Previous Sales]
WHERE ((([Previous Sales].Price) Between [Lower Fence] And [Upper Fence]))
GROUP BY [Previous Sales].[Car Year], [Previous Sales].Make, [Previous Sales].Model;

Thank You,

[ssanfu]

Could you please explain what do you mean by not including spaces? in an example please.

Your table name: Previous Sales
My table name: PreviousSales or Previous_Sales

Your field name: Car Year
My field name: CarYear or Car_Year

See: http://access.mvps.org/access/tencommandments.htm (Rule #3)

[ssanfu]

I don't know if this is what you want, but I used two queries:

Query1

SELECT [Previous Sales].[car Year], [Previous Sales].Make, [Previous Sales].Model, Xpercentile("Price","[Previous Sales]",0.25) AS Quartile1, Xpercentile("Price","[Previous Sales]",0.5) AS Quartile2, Xpercentile("Price","[Previous Sales]",0.75) AS Quartile3, [Quartile3]-[Quartile1] AS IQL, [Quartile1]-1.5*[IQL] AS [Lower Fence], [Quartile3]+1.5*[IQL] AS [Upper Fence], Avg([Previous Sales].Price) AS AvgOfPrice
FROM [Previous Sales]
GROUP BY [Previous Sales].[car Year], [Previous Sales].Make, [Previous Sales].Model;

Query2:

SELECT Query1.[car Year], Query1.Make, Query1.Model, Query1.Quartile1, Query1.Quartile2, Query1.Quartile3, Query1.IQL, Query1.[Lower Fence], Query1.[Upper Fence], Query1.AvgOfPrice
FROM Query1
WHERE (((Query1.AvgOfPrice) Between [Lower Fence] And [Upper Fence]));

[gtg430i]

I figured out. Thank you

[ssanfu]

Great! Care to share your solution?


Also, are you ready to mark this solved??

[gtg430i]

It's solved. How do I mark it as so?

[ssanfu]

Top of the page, under "Thread Tools" (in the green bar)