Creating Ranks in a query

[Tlewman]

I have got an access query that has three columns showing monthly sales by state-
Month State Sales

I append data to the table that this info pulls from each month so every month, I am adding all 50 states with the sales for that month. In my query, I only want to look at the current month so I have a prompt on the month field.

My question: Is there a way to add a column in this query or in a form/report to automatically rank the states based on sales? I want to keep my states in alphabetical order but be able to show how they rank overall compared to all other states.

I've got a brain block and cannot figure this out.
Thanks!

 

[Corticus]

Welcome!

Perhaps I am oversimplifying this, but,

Couldn't you just make a query that has 2 fields, the state, and the sales, and then just sort by sales ascending?

Let me know if I'm totally off...


HTH,

 

[Tlewman]

I have a report that pulls from this query as well that shows each state by month with their sales and the % increase/decrease from the previous month. I need to add the ranking to this report but I only want it to rank the most recent month.

For example:

The left most column is the list of states in alphabetical order, the months run across the top and below each month, I need to show sales, % increase/decrease over the previous month and for the most recent month, I need to show the ranking. I am thinking that I will add the field across the top after the last month (Dec) and when I go to print the report, when it prompts me for the current month, it can update the ranking if possible.

Does this make any sense? I am starting to confuse myself.

 

[Corticus]

I think I get what your saying,

You'll have sales for 12 months, but the order in which the states are listed should be determined be their sales rank for the last month. Is your query returning results with states as records and months as fields, or do you have states as records, with a date field, and the reports organize the months into columns?

-Corticus

 

[Tlewman]

Report is built off of a crosstab query that has my states as a row heading, my month as column headings and my sales info as a value. The states always need to appear in alphabetical order.
ex:
Jan Feb Mar Apr May etc rank (based on last mo)
AL 500 550 2
AK 485 490 4
AZ 590 600 1
AR 560 570 3 etc
CA
CO

I update this monthly so when I receive info for Mar, it is appended to the underlying table which updates the query and then the report. I need to find a way to update the rank field automatically based on the most recent months (which will always be last month) data.

 

[Corticus]

Ahh I get it now,

The states need to stay in ABC order, you just need to see a rank field that displays the rank of the states sales for the last month.

I can't think of anything off hand , but don't dispair, I'll try to come up with something soon, and if not me, maybe someone else.

edit: I got your cross tab query built, now the fun part...

-C