QUESTION REGARDING QUARTILING

[cvkd]

Hello All!! I am new to this forum and would appreciate any help. I have information regarding 30 different stores, i.e. gross sales, wages, store operations, profit etc... I have imported all the info into access tables. I want to run a query that will tell me what quartile 1st, 2nd, 3rd or 4th each stores info for each criteria lies compared to the other stores.

I did it in excel by using the MAXA statement for a group like gross sales for all stores, then dividing each field under gross sales to come up with a percentage. From that i then wrote a if statement =IF(C2>0.75, 4, IF(C2>0.5, 3, IF(C2>0.25, 2, IF(C2>0, 1)))) where c2 represents a field for a store under gross sales.

I want to automate it in access rather than having to create excel tables every time when comparing year over year... is there any way in access to do all this?

thanks,

cvkd

 

[Russell Hauf]

Hi, welcome to the board.

I'm afraid that more information is needed. One thing that might be helpful would be an example of how your data looks now, and an example of how you want it to look.

Thank you,

Russell

 

[LTunnicliffe]

I think what he wants is something like:

Book3
	A	B	C	D	E
4
5		4	4.0%	1
6		5	5.1%	1
7		67	67.7%	3
8		8	8.1%	1
9		99	100.0%	4
10		10	10.1%	1
11		9	9.1%	1
12		0	0.0%	1
13
Sheet1

I'm kinda curious myself...

 

[cvkd]

QUESTION REGARDING QUARTILING...more info

LTunnicliffe,

thanks for the demo, that is exactly how i have it set up in excel, but what i want to do create that in access in order to automate the reporting. THat way, every year i can run a quartile report on high-level entries without having to create a separate table of just quatiles and having to import that into access to tie together with the original tables...

any ideas would be great!

very primatively i want it to look like this

store number__ sales:::quartile___wages:::quartile___net profit:::quartile

700 500 2 100 1 400 2
701 200 1 100 1 100 1
702 750 3 500 2 250 2
703 1000 4 800 4 200 2

etc...etc..

hope this helps, what i need is how to write it in access.

thanks

 

[Russell Hauf]

I would probably do it something like this:

First, I would set up a table something like:

<table border="1"><col width="64" span="2" style="width:48pt"><tr height="14" style="height:10.5pt"><td height="14" class="xl24" width="64" style="height:10.5pt;width:48pt">fldNum</td><td class="xl24" width="64" style="border-left:none;width:48pt">fldQrt</td></tr><tr height="14" style="height:10.5pt"><td height="14" class="xl25" style="height:10.5pt;border-top:none" x:num>0.00</td><td class="xl24" style="border-top:none;border-left:none" x:num>1</td></tr><tr height="14" style="height:10.5pt"><td height="14" class="xl25" style="height:10.5pt;border-top:none" x:num>0.25</td><td class="xl24" style="border-top:none;border-left:none" x:num>2</td></tr><tr height="14" style="height:10.5pt"><td height="14" class="xl25" style="height:10.5pt;border-top:none" x:num>0.50</td><td class="xl24" style="border-top:none;border-left:none" x:num>3</td></tr><tr height="14" style="height:10.5pt"><td height="14" class="xl25" style="height:10.5pt;border-top:none" x:num>0.75</td><td class="xl24" style="border-top:none;border-left:none" x:num>4</td></tr></table>

Then, for each quartile you want, you'd do something like:

• SalesQuartile: DLookUp("[fldQrt]","tbl071503Qrt","[fldNum] = " & DMax("[fldNum]","tbl071503Qrt","[fldNum] < " & [Sales]/DMax("[Sales]","tbl071503")))

My "main" table name is tbl071503 and my "quartile" table (like the one shown above) name is tbl071503Qrt...change these to your names of course. For the Wages and Net Profit quartiles, just substitute those field names for Sales in the calculated field above (and change its name from SalesQuartile...).

BUT - if all quartiles are based on Sales (so the Wages quartile is based on the max number in the Sales field), then you would leave [Sales] in the last DMax part above...in your example it looks like the Wages and Net Profit quartiles are based on the Sales number. Just out of curiosity I'd like to know which way you do it.

Thank you, and I hope this helps,

Russell