QUERY

[Gambler]

I made up a formula in one of my query fields and it works fine. What i would like to do is give its numbers a ranking so I could see if the winner was in my top 4 rankings. I would like the largest number to be ranked number one etc. Is there ant way I can do this?

Thanks ed

[Donnie]

Ed-

Go here and read thru this page...you should be up and at 'em in a few minutes!

http://support.microsoft.com/default.aspx?scid=KB;en-us;q208946

Best of Luck!

[Gambler]

Thanks Donnie. Ill give it a try.

[Gambler]

What am I doing wrong?
Im using example 1.
Make new query with the field I want to rank.
Set the field list Alias to Emp.1
I put my EXPR1 (these are the numbers I want to rank.) in the query Field Row.
Set sort to ascending’
In step 6 it said to put the following expression in the third row field column. Seniority (select count (*) from Employees where----------etc.
I let column two blank.

What am I doing wrong.

[hurrikane]

Are you changing your table/field names to match your tables and fields?

[Donnie]

Ed--
I have been out of town training....I will get a chance to look into it tomorrow! I actually have a prep day at work tomorrow!!

[Rick]

This is what I tried and the results.

I added a field named Mine to ROI analysis query. Simple query adding nFr1 and nFr2 together.

Using the ROI analysis query instead of a table I tried making this query. Under Alias I used ROIa.

SELECT ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM, ROIa.Mine, (Select Count (*) from [ROI analysis] Where [Mine] > [ROIa].[Mine]+1 ;) AS MineRank
FROM [ROI analysis] AS ROIa
ORDER BY ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM;


It does a rank but doesn't break it down by race.

Anyone have any ideas how to get it to do a rank by race?

:confused:

[Rick]

I found this in an Access Newsgroup:
"Joseph Bloch" <jbloch@surveyusa.com> wrote in message
news:df7d2c1c.0410110803.4724885@posting.google.co m...
> In all the threads on ranking in Access queries, I've not seen
> anything that deals with ranking against a subset of the query
> results. What I need to do is take the following query results:
>
> Dept Subdept Amount
> AAA A1 75
> AAA A2 13
> AAA A3 45
> BBB B1 4
> BBB B2 16
> CCC C1 20
> CCC C2 22
> CCC C3 15
>
> And rank the Subdept field _within_ the Dept field, so I would get the
> following results:
>
> Dept Subdept Amount Rank
> AAA A1 75 1
> AAA A2 13 3
> AAA A3 45 2
> BBB B1 4 2
> BBB B2 16 1
> CCC C1 20 2
> CCC C2 22 1
> CCC C3 15 3
>

Here is one way:

select a.Dept, a.Subdept, a.Amount, count(*) as rank
from
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as a
inner join
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as b on a.Dept = b.Dept and a.Amount <= b.Amount
group by a.Dept, a.Subdept, a.Amount
order by a.Dept, a.Subdept, count(*)


You should be aware of how this handles ties, (if two rows tie for 1st place
it will give them each a ranking of 2). If that's not the behaviour you need
then you will have to modify the query or use a different method.
Might give someone an idea how to proceed.

I will probably try playing with it later. :D

[Rick]

Ed,
I just saw your question in microsoft.public.access.queries and the response you got.

Be sure to include the information that the table is broke down into races with the fields tDATE, tTRK, and nRACE. and that you are trying to get the ranking within each race.

Looks like some people really know what they are doing in that group, at least the MS IT people. :D

[Rick]

SELECT ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM, ROIa.Mine, (Select Count (*) +1 from [ROI analysis] Where [ROI analysis].[tDATE] = [ROIa].[tDATE] AND [ROI analysis].[tTRK] = [ROIa].[tTRK]
AND [ROI analysis].[tPGM] = [ROIa].[tPGM] AND [Mine] > [ROIa].[Mine]+1 ;) AS MineRank
FROM [ROI analysis] AS ROIa
ORDER BY ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM;

This appears to work.

Just go back and read my first message here and replace SQL View of query with this one.

[Gambler]

Where did the ROIa come from? Ive never used it.
Thanks for helping. ed

[Donnie]

Ed-
Those would be the table names followed by the field names you would use....HX4.tTRK = tableName.fieldName

It's called dot notation....the field name to the left of the period, the field name to the right of the period....just use your table names an field names

[Rick]

That came from 1st trying to use the info from the link that Donnie gave for "How to Rank Records Within a Query".

That is the name I used for Steps 2 and 3 under example 1.

I started with the instructions there. I was able to get a ranking but the ranking was for my whole set of records.

That was when I went looking in the Access Newsgroups to see if I could find any info that might help me just do the ranking by race.

I just started building onto what I had already started.

A twisted trip.

I was able to put it together through trial and error, but I sure don't understand all the step.

Continue your quest through the Newsgroups. You will probably get a more elegant answer.

[Donnie]

Rick-
What you have written is a sub-query. I haven't looked thru your SQL closely yet, but I would tend to think it is accurate if you said it works. The key I believe here to do it another way would be to group by track and race. This is done in the reports most of the time, but can be written in the query as well by using a total query. Gonna go paste your SQL into a word processor to see it all at once with no scroll....me no like scroll!

[Rick]

After further review, my version is not working correctly.

When someone gets it working correctly, please post.

[Rick]

SELECT ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM, ROIa.Mine, (Select Count (*)+1 from [ROI analysis] Where [ROI analysis].[tDATE] = [ROIa].[tDATE]
AND [ROI analysis].[tTRK] = [ROIa].[tTRK] AND [ROI analysis].[nRACE] = [ROIa].[nRACE] AND [Mine] > [ROIa].[Mine];) AS Rank
FROM [ROI analysis] AS ROIa
ORDER BY ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.Mine DESC;
This appears to work correctly.

I have it set to display in rank order

[Bob]

I have fiddled for 2 hours trying to get it to work. Ugh!

Here is what I have:

My table name is 'Bob' that contains the field 'BOB' that has my numbers that I want to rank by race.

How does the above fit into your formula?

I substitued 'Bob' for ROIa and 'BOB' for 'mine' but,
I am not sure what [Roi Analysis] is pulling from?

Thanks in advance for any help you can give.

[Rick]

SELECT ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.tPGM, ROIa.Mine, (Select Count (*)+1 from [ROI analysis] Where [ROI analysis].[tDATE] = [ROIa].[tDATE]
AND [ROI analysis].[tTRK] = [ROIa].[tTRK] AND [ROI analysis].[nRACE] = [ROIa].[nRACE] AND [Mine] > [ROIa].[Mine];) AS Rank
FROM [ROI analysis] AS ROIa
ORDER BY ROIa.tDATE, ROIa.tTRK, ROIa.nRACE, ROIa.Mine DESC;

I finally got it to work. After two years I had no idea what was going on here.

In the above replace the Mine with the name bob if that is the name of the field in the query you want ranked.

Replace the [ROI analysis] with [bob] if bob is the name of the query that includes the field bob.

The ROIa is just an Alias for the query.

If your field bob is in a table you should be able to just put the table name instead of the query name in there.

[Donnie]

...where rick's code below shows a winking smiley...that's a semicolon followed by a closing parentheses. It creates a smiley face on the board.....see ;)

[Rick]

Thanks Donnie. I didn't notice that.

[Bob]

Rick/Donnie-

Thanks guys, I finally got it to work.

It runs slow as heck but I am finally getting some research done.

Thanks again :)

[Rick]

You might try making a table with your computed field (bob). That way you can index that field.

I don't think its possible to index a field in a query. But of course I could be wrong.

That should speed up running the query.

Or else, contact MikeDee and have him send you his VBA module for ranking.