ranking records

[easyit]

Hi,

I need to rank records.
If the field [100%] is the same for more records (rows), the colums volume 2009-2011 are used for ranking. first the column volume2011 is used, the highest value gets the highest ranking. If they are the same as well, the column volume 2011 is used, and then volume 2009.

for the ranking I need to add a percentage (0,0001%) to the highest, less to the lower ones. Or maybe is ordering enough - any solution is welcome.

Can anybody point me in a working direction?

example data:

fk_pi 100%	sector opl	Naam	                 vol2011	vol2010	vol2009
4804	6,71%	GGZVS GGZ	Altrecht	           905	1044	1045
4804	6,71%	GGZVS GGZ	Pro Persona	        905	1044	767
4804	6,71%	GGZVS GGZ	GGZ Noord-H	        905	616	614
5271	6,71%	GGZVS GGZ	GGZ Friesland	      575	614	605
5271	6,71%	GGZVS GGZ	Rivierduinen	       575	702	714
5443	5,03%	GGZVS GGZ	Yulius	             468	499	318
5443	5,03%	GGZVS GGZ	GGzE	               468	499	474
5443	5,03%	GGZVS GGZ	GGz Breburg	        468	499	584
5443	5,03%	GGZVS GGZ	Emergis	            468	499	318

EasyIT

"Do you think that’s air you're breathing?

 

[TheAceMan1]

Howare ya easyit . . .

Unless I'm missing something you appear to have the resource for a simple order by/sort, just needs to be done in the [blue]right order[/blue] and [blue]descending[/blue]. In an SQL statement it would look like:

[blue]ORDER BY [100%] DESC, [vol2011] DESC, [vol2009] DESC;[/blue]

Also ... not a good idea using 100% as a fieldname.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[easyit]

Acmanen1,

Thanks for the response. No, it is not as straight forward. The list has several hundred records of which some are equal in the [100%] field, most are not and hence need for the ranking based on the other columns.

Also, how do I raise the [100%] field by a small percentage? for the records it involves I mean...



EasyIT

"Do you think that’s air you're breathing?

 

[TheAceMan1]

easyit . . .

I see! Out of curiousity ... what would the ranking be if there were no duplicates?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[easyit]

...within each [sector opl] the [100%] score (which in itself is already a calculated field). It is not alphabetical, the ranking translates to money in the end... ordering by names would make some people angry.



EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

hmmm

Tried to create duplicate records query, which works fine. Then another based on the latter. Than results in a "unknown Access Error". And a third query finding the dups in the second also ofcourse.

Is there a limit on building duplicate queries?

FIRST:

SELECT [TW2013_2B-b_1].[sector opl], [TW2013_2B-b_1].[100%], [TW2013_2B-b_1].[volume 2011], [TW2013_2B-b_1].fk_praktijkinstelling, [TW2013_2B-b_1].Naam, [TW2013_2B-b_1].[#], [TW2013_2B-b_1].behoefte, [TW2013_2B-b_1].historisch, [TW2013_2B-b_1].laagste, [TW2013_2B-b_1].totaal, [TW2013_2B-b_1].plafond, [TW2013_2B-b_1].[Trekkingsrecht niet afgerond], [TW2013_2B-b_1].[TW afgerond], [TW2013_2B-b_1].[volume 2010], [TW2013_2B-b_1].[volume 2009]
FROM [TW2013_2B-b_1]
WHERE ((([TW2013_2B-b_1].[sector opl]) In (SELECT [sector opl] FROM [TW2013_2B-b_1] As Tmp GROUP BY [sector opl],[100%],[volume 2011] HAVING Count(*)>1  And [100%] = [TW2013_2B-b_1].[100%] And [volume 2011] = [TW2013_2B-b_1].[volume 2011])))
ORDER BY [TW2013_2B-b_1].[sector opl], [TW2013_2B-b_1].[100%], [TW2013_2B-b_1].[volume 2011];

SECOND:

SELECT [TW2013_2B-b_1_dup1].[sector opl], [TW2013_2B-b_1_dup1].[100%], [TW2013_2B-b_1_dup1].[volume 2010], [TW2013_2B-b_1_dup1].[volume 2011], [TW2013_2B-b_1_dup1].[fk_praktijkinstelling], [TW2013_2B-b_1_dup1].[Naam], [TW2013_2B-b_1_dup1].[#], [TW2013_2B-b_1_dup1].[behoefte], [TW2013_2B-b_1_dup1].[historisch], [TW2013_2B-b_1_dup1].[laagste], [TW2013_2B-b_1_dup1].[totaal], [TW2013_2B-b_1_dup1].[plafond], [TW2013_2B-b_1_dup1].[Trekkingsrecht niet afgerond], [TW2013_2B-b_1_dup1].[TW afgerond], [TW2013_2B-b_1_dup1].[volume 2009]
FROM [TW2013_2B-b_1_dup1]
WHERE ((([TW2013_2B-b_1_dup1].[sector opl]) In (SELECT [sector opl] FROM [TW2013_2B-b_1_dup1] As Tmp GROUP BY [sector opl],[100%],[volume 2010] HAVING Count(*)>1  And [100%] = [TW2013_2B-b_1_dup1].[100%] And [volume 2010] = [TW2013_2B-b_1_dup1].[volume 2010])))
ORDER BY [TW2013_2B-b_1_dup1].[sector opl], [TW2013_2B-b_1_dup1].[100%], [TW2013_2B-b_1_dup1].[volume 2010];

THIRD:

SELECT [TW2013_2B-b_1_dup2].[sector opl], [TW2013_2B-b_1_dup2].[100%], [TW2013_2B-b_1_dup2].[volume 2009], [TW2013_2B-b_1_dup2].[volume 2010], [TW2013_2B-b_1_dup2].[volume 2011], [TW2013_2B-b_1_dup2].[fk_praktijkinstelling], [TW2013_2B-b_1_dup2].[Naam], [TW2013_2B-b_1_dup2].[#], [TW2013_2B-b_1_dup2].[behoefte], [TW2013_2B-b_1_dup2].[historisch], [TW2013_2B-b_1_dup2].[laagste], [TW2013_2B-b_1_dup2].[totaal], [TW2013_2B-b_1_dup2].[plafond], [TW2013_2B-b_1_dup2].[Trekkingsrecht niet afgerond], [TW2013_2B-b_1_dup2].[TW afgerond]
FROM [TW2013_2B-b_1_dup2]
WHERE ((([TW2013_2B-b_1_dup2].[sector opl]) In (SELECT [sector opl] FROM [TW2013_2B-b_1_dup2] As Tmp GROUP BY [sector opl],[100%],[volume 2009] HAVING Count(*)>1  And [100%] = [TW2013_2B-b_1_dup2].[100%] And [volume 2009] = [TW2013_2B-b_1_dup2].[volume 2009])))
ORDER BY [TW2013_2B-b_1_dup2].[sector opl], [TW2013_2B-b_1_dup2].[100%], [TW2013_2B-b_1_dup2].[volume 2009];

EasyIT

"Do you think that’s air you're breathing?