Query Rank

[DHIGHTO]

Hey guys,

I'm trying to rank some results from a query. I need to rank a count from my field (REASON_CODE) with another field (Agency names). I'm pretty sure I need to use SQL for this, but I'm not the greatest with SQL. I'm not sure if I need to provide anymore information.

Thanks

DATE	AGENCY	CountOfREASON_CODE
12/1/2014	AGENCY1	201
12/1/2014	AGENCY2	165
12/1/2014	AGECNY3	138

[lfpm062010]

Maybe this will help.

http://allenbrowne.com/ranking.html

[orange]

There is not much detail in your post. You may simply need an Order By in your SQL. It would be helpful to readers if you could describe what you are trying to achieve, and what you have tried, and any shortfalls in the result.

[DHIGHTO]

I'm looking to rank each agency based on the highest number (countofreasoncode). For example, agency1 has rank 1, agency2 ranked 2, and agency ranked 3. I have other counts so I can't just use a static 1-3.

Im looking to be able to generate a query like this. I hope this is clear.

Agency	# TPD's Submitted	submitted Rank	# TPD's Indefinite	indefinite rank
agency 1	102	2	454	4
agency 2	504	1	245	5
agency 3	36	5	978	1
agency 4	55	3	744	2
agency 5	45	4	489	3

[orange]

Just to help put your post into context. We have no idea of what a TPD is nor how a submitted TPD differs from an indefinite TPD.
Similarly a Submitted Rank versus an Indefinite Rank is a pure guess by the reader.

Can you step back and with simple clear English tell the reader WHAT is your Business, WHAT are you trying to accomplish?
Consider that we do not know you, we do not know your business and we have no experience with your environment. We only know what you tell us. We are familiar with How to do things and even provide options in some cases. But we have to know WHAT before we can suggest/recommend HOW.

Also, please describe your Rank terminology.

[rpeare]

I don't think it matters orange, here's an example.

TAKE NOTE: the queries use DOMAIN FUNCTIONS which I ordinarily do not suggest you use for queries because they are memory hogs and run slowly for large datasets, but sort of appending data to temporary tables just for reporting (a practice I also do not like) this is the only way I can think of:

DIGHTO.zip

[DHIGHTO]

Ok, I'll try again.

I'm making a query that will be used for a report that has agencies ranked on the count of differnet TPD statuses. Two statuses are TPD 120SUSP and TPD INDEFSUSP, which are called reason codes. In my query, I am counting the reason codes, which I have called COUNTOFREASON_CODE. Based on the count of each status, the agencies get ranked 1-17 for each status. Reason code TPD 12SUSP and TPD INDEFSUSP each have a rank associated. Rank is based on the highest number then descending.

RANKING: (Select Count(*) from [TPD 120SUSP] Where [COUNTOFREASON_CODE] > _
[TPD_NELNET_TRACKER].[COUNTOFREASON_CODE])+1

This is the SQL I'm failing with. I'm attemped to just count TPD 120 SUSP in this SQL. [TPD 120SUSP] is a query. [TPD_NELNET_TRACKER] is the table that holds the data.

I know this is confusing since I having a hard time explaining it.
Thanks for your patience!

Agency	120SUSP	120SUSP RANK	INDEFSUSP	INDEFSUSP RANK
AGENCY 1	31	5	11	3
AGENCY 2	54	1	15	1
AGENCY 3	25	10	7	11
AGENCY 4	47	2	12	2
AGENCY 5	31	5	10	5
AGENCY 6	22	11	4	16
AGENCY 7	15	15	10	5
AGENCY 8	16	14	8	9
AGENCY 9	21	12	5	15
AGENCY 10	29	7	4	16
AGENCY 11	21	13	9	8
AGENCY 12	32	4	8	9
AGENCY 13	33	3	10	5
AGENCY 14	27	9	7	11
AGENCY 15	9	16	6	13
AGENCY 16	9	16	6	13
AGENCY 17	28	8	11	3

[DHIGHTO]

I'm basing my SQL code off of below

Ranking: (Select Count(*) from ProductCount Where [CountofProductID] > _
[Prod1].[CountofProductID]) + 1

I'm not sure if ProductCount is a table or query, and I don't know what [Prod1] comes from.

[DHIGHTO]

I don't think it matters orange, here's an example.

TAKE NOTE: the queries use DOMAIN FUNCTIONS which I ordinarily do not suggest you use for queries because they are memory hogs and run slowly for large datasets, but sort of appending data to temporary tables just for reporting (a practice I also do not like) this is the only way I can think of:

DIGHTO.zip

I used your db as a template. I put this in the expression builder.

Expr1: DCount([CountOfREASON_CODE],[TPD_NELNET_TRACKER],[CountOfREASON_CODE<] & [CountOfREASON_CODE])+1

I getting a enter parameter value for TPD_NELNET_TRACKER. I'm not sure what I did wrong.

Thanks

[rpeare]

sample database please, I don't want to attempt to recreate your naming conventions

[orange]

rpeare
I realize the details aren't relevant. I really wanted to see if the OP could put the requirement into simple English.
Does he/she understand the issue sufficiently well to describe it to the person next in line at McDonalds. When someone has trouble describing an issue to others, it's often a sign of more analysis needed --not always, but often.

[DHIGHTO]

Here is the template I used

SubRank: DCount("tpdindef","tbltest","tpdindef < " & [tpdindef])+1

Here is mine

Expr1: DCount([CountOfREASON_CODE],[TPD_NELNET_TRACKER],[CountOfREASON_CODE<] & [CountOfREASON_CODE])+1

EXPRESSION = CountOfREASON_CODE

TABLE = TPD_NELNET_TRACKER

CRITERIA = CountOfREASON_CODE<

[rpeare]

When two agencies have the same score their rank should be the highest value yet available and the next agency in line would have the rank increased by 2 not 1. i.e. Agency 1 and 5 should both have the rank 5, while agency 10 should be rank 7th

The formula I gave you will work. Just create a query based on the query that gives the AGENCY 120SUSP and INDEFSUSP fields. In this new, ranking query, add two fields following the formula I gave you using the field names 120SUSP and INDEFSUSP.

Agency	120SUSP	120SUSPRank	INDEFSUSP	INDEFSUSPRank
Agency 1	31	5	11	3
Agency 2	54	1	15	1
Agency 3	25	9	7	11
Agency 4	47	2	12	2
Agency 5	31	5	10	5
Agency 6	22	10	4	16
Agency 7	15	14	10	5
Agency 8	16	13	8	9
Agency 9	21	11	4	16
Agency 10	29	7	5	15
Agency 11	21	11	9	8
Agency 12	32	4	8	9
Agency 13	33	3	10	5
Agency 14	27	8	7	11
Agency 15	9	15	6	13
Agency 16	9	15	6	13
Agency 17	8	17	11	3

[DHIGHTO]

rpeare

My expression is only generating "1" ranks. Below is my expression. Not sure where I went wrong.

DCount("CountOfREASON_CODE","tpd_nelnet_tracker"," CountOfREASON_CODE<" & [CountOfREASON_CODE])+1

Thanks

[DHIGHTO]

I think my query was too large since I had a query to find the numbers plus a ranking expression. I have figured it out and just wanted to thank you guys for the input and patience to deal with a learning database admin. I really appreciate it!