Rank query multiple criteria.

[redxtb]

I have a table that has 108076 rows and 8 fields wide 4 percents, 1 currency, 2 numeric (store & deptNumber) and 1 text.

i need to rank each store(1500) by dept(74) Sales$. I have tried this

Rank: (select count(*) from [qryTop10Dept] where [qryTop10Dept]![Store TYTD Sales $]>= [cw]![Store TYTD Sales $] and [cw]![Store #] = [Store #]


but is taking way too long... Yes i know you should not use # and $'s but you have to use what you have been given...

Any ideas? i am not afraid of VB, but at kind of a loss now so my apologies if this is in the wrong forum!!

 

[jdraw]

Can you post the database, or pass us some data?

Can you mock up a sample of desired output?

I helped someone with a rank query here (yesterday)

 

[redxtb]

Hey jdraw,

I looked at your link and that is what i tried to do already, but it ran for 1 hour with no results. i have 5 of these tables and need them to run very quickly. probably less than 1 hour for all. the first 3 columns is all that is really necessary in this query the others are just data that needs to be added in the report.

Store No Dept No Store TYTD Sales $ Store LYTD Sales $ Sales$ Comp LY Sales$ Comp curYoyChange perComp
102 82 $7,850 $7,850 7849.99 -100.00%
102 145 $69,233 $55,080 $69,233 $55,080 14152.53 25.69%
102 159 $24,798 $21,240 $24,798 $21,240 3557.67 16.75%
102 182 $15,713 $11,725 $15,713 $11,725 3988.19 34.01%
102 200 $98,950 $81,796 $98,950 $81,796 17153.69 20.97%
102 272 $22,554 $14,578 $22,554 $14,578 7976.06 54.71%
102 273 $28,056 $19,897 $28,056 $19,897 8159.18 41.01%
102 350 $18,923 $12,270 $18,923 $12,270 6653.34 54.23%
102 365 $15,525 $11,572 $15,525 $11,572 3953.05 34.16%
102 386 $10,980 $4,430 $10,980 $4,430 6549.82 147.86%
102 492 $4,699 $10,110 $4,699 $10,110 -5411.91 -53.53%
102 547 $20,831 $11,159 $20,831 $11,159 9671.91 86.68%
102 592 $6,823 $2,019 $6,823 $2,019 4804.34 238.00%
102 667 $11,508 $15,591 $11,508 $15,591 -4082.86 -26.19%
102 733 $51,814 $34,148 $51,814 $34,148 17666.85 51.74%
102 734 $18,850 $27,748 $18,850 $27,748 -8898.09 -32.07%
102 753 $29,901 $24,497 $29,901 $24,497 5404.79 22.06%
102 763 $35,850 $24,799 $35,850 $24,799 11050.91 44.56%
102 771 $51,800 $36,199 $51,800 $36,199 15600.94 43.10%
102 772 $8,850 $14,378 $8,850 $14,378 -5527.89 -38.45%
102 774 $13,063 $9,383 $13,063 $9,383 3679.92 39.22%
102 796 $15,578 $10,409 $15,578 $10,409 5169.05 49.66%
100 79 $27,967 $20,264 $27,967 $20,264 7702.26 38.01%
100 82 $53,395 $53,395 53394.89 -100.00%
100 145 $40,486 $28,514 $40,486 $28,514 11972.41 41.99%
100 163 $49,193 $55,444 $49,193 $55,444 -6250.88 -11.27%
100 200 $145,504 $118,640 $145,504 $118,640 26864.25 22.64%
100 204 $38,215 $26,322 $38,215 $26,322 11893.34 45.18%
100 205 $51,328 $45,098 $51,328 $45,098 6230.55 13.82%
100 218 $30,330 $19,532 $30,330 $19,532 10798.01 55.28%
100 352 $39,668 $25,331 $39,668 $25,331 14336.9 56.60%
100 386 $19,134 $9,825 $19,134 $9,825 9309.77 94.76%
100 546 $13,059 $7,147 $13,059 $7,147 5912.89 82.74%
100 547 $29,663 $22,519 $29,663 $22,519 7144.03 31.72%
100 667 $16,204 $10,406 $16,204 $10,406 5797.6 55.71%
100 668 $22,753 $16,554 $22,753 $16,554 6199.04 37.45%
100 748 $28,081 $19,663 $28,081 $19,663 8417.82 42.81%
100 763 $56,845 $41,005 $56,845 $41,005 15839.84 38.63%
100 771 $207,081 $175,715 $207,081 $175,715 31366.21 17.85%

dept needs to be ranked in store. Store 102 would be ranked 1-23 (dept 200 would be #1) and store 100 (dept 771 would be #1) would be ranked 1-17

Thanks for taking the time to look at. I think i need to do dome kind of update or make table query.....

 

[jdraw]

I looked at your data. Used the first 3 fields are you suggested.
Loaded the resulting data into a table
StoreSales (39 records)

 	ID		         0	Long	4	
	StoreNo		 1	Long	4	
 	DeptNo		 2	Long	4	
        StoreTYTDSales	 3	Long	4

and used this query

SELECT *
FROM (
	SELECT a1.Storeno
		,a1.DeptNo
		,a1.StoreTYTDSales
		,COUNT(*) AS CategoryRank
	FROM [StoreSales] AS a1
	INNER JOIN [StoreSales] AS a2 ON (a1.Storeno = a2.Storeno)
		AND (a1.StoreTYTDSales <= a2.StoreTYTDSales)
	GROUP BY a1.Storeno
		,a1.DeptNo
		,a1.StoreTYTDSales
	) AS RankingQuery
WHERE (((RankingQuery.[CategoryRank]) <= 25))
ORDER BY RankingQuery.Storeno
	,RankingQuery.StoreTYTDSales DESC
	,RankingQuery.DeptNo
	,RankingQuery.CategoryRank;

with result:

Storeno	DeptNo	StoreTYTDSales	CategoryRank
100	771	207081	1
100	200	145504	2
100	763	56845	3
100	82	53395	4
100	205	51328	5
100	163	49193	6
100	145	40486	7
100	352	39668	8
100	204	38215	9
100	218	30330	10
100	547	29663	11
100	748	28081	12
100	79	27967	13
100	668	22753	14
100	386	19134	15
100	667	16204	16
100	546	13059	17
102	200	98950	1
102	145	69233	2
102	733	51814	3
102	771	51800	4
102	763	35850	5
102	753	29901	6
102	273	28056	7
102	159	24798	8
102	272	22554	9
102	547	20831	10
102	350	18923	11
102	734	18850	12
102	182	15713	13
102	796	15578	14
102	365	15525	15
102	774	13063	16
102	667	11508	17
102	386	10980	18
102	772	8850	19
102	82	7850	20
102	592	6823	21
102	492	4699	22

 

[redxtb]

Jdraw Works Great!!! Thanks a lot!! can you explain a little bit? where i can use in other situations?

 

[jdraw]

You can use it as a template/guide for doing Ranking within Groups.
Allen Browne has articles on subqueries here and here.

Sample MsAccess Ranking Queries from Bytes

Smart Access - subquery techniques and examples