Adding a Rank column to a report

[OtheymAverian]

Using Access '02, Windows XP Pro SP2

I have a Report that Contains data that is sorted by name and have been asked to add a field that would output a rank based on a numerical (currency) field.

Example Report structure as it is now (dashes added for spacing purposes)

Name-----------Score
----------------------------
ABC------------------10
BCD------------------25
CDE--------------------5

Example Report Structure needed

Rank------Name--------------Score
------------------------------------------
---2---------ABC-------------------10
---1---------BCD-------------------25
---3---------CDE-------------------5

I just started my adventure into Access a few weeks ago and have no SQL knowledge. I have attempted to make a query to pull data, resort, and number, but I have been unable to get my head around some of the comcepts. I do not know if it is possible to add a field into a query that would auto count a rank (based on the query being sorted by the Score field)

[Rabbit]

That worked perfectly. It kind of sucks to have to take the extra step to generate the table, but I am much further along than I was before I found you guys. Many many thanks.

-Oth

Not a problem, good luck.

Yes, the extra step is a hassle but if you're going to make it user friendly and have a switchboard, you can basically automate everything through code.

[OtheymAverian]

All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.

-Oth

[NeoPa]

All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.

-Oth

Try to avoid ever using spaces or punctuation characters in names too.
On the ranking issue, other than the fact that this obviously works, I would have sworn that a subquery couldn't reference a recordset name (Temp in this case) defined outside of the subquery itself. I tried it myself and it works perfectly - nice one.
Thank you Rabbit for this lesson :)

[Rabbit]

I found it online somewhere, it was for ascending so had to change it a bit for descending.

[NeoPa]

I've always found a reference to a dataset (FROM clause) in the outer query cannot be used within the subquery. I suspect that this was due to my generally placing my subqueries within the FROM clause of the outer query though. It's very interesting that subqueries in the SELECT and WHERE clauses can run through (with different data) every time they are used (I mean for each record of the outer query).

[OtheymAverian]

If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?

I am looking to remove the spaces from my field names and rename the [Date] field to something more appropriate.

-Oth

[NeoPa]

If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?

Yes it could.
I would certainly make a mental note to create new ones in future which match those criteria.
As for amending existing names, that's up to you. For me it would depend on how much of a problem would be caused by the knock-on effect.

[OtheymAverian]

Is there a way to disable or auto approve the prompts generated by running a make table query? I have been looking through the posts for something, but I do not know what exacty to search for.

-Oth

[NeoPa]

Call DoCmd.SetWarnings(False)
{Your action query(s) here}
Call DoCmd.SetWarnings(True)

[OtheymAverian]

Where do I put that code? I only need it when runnig one specific query.

-Oth