Descending Count for Each Record in Query (1 Viewer)

[bmcgree1]

I have a query that shows everything I want it to except give me a Descending count. I have my criteria on it and it's sorted the way I want, now I just want to add another column that begins at 1 and counts down from there.

i.e. - If there are 5 records in my query, this new column will show 1,2,3,4,5 in each field from the top down.

Does anyone know how to accomplish this? Your help is greatly appreciated. Thank you.

 

[orchbelt00]

I have a query that shows everything I want it to except give me a Descending count. I have my criteria on it and it's sorted the way I want, now I just want to add another column that begins at 1 and counts down from there.

i.e. - If there are 5 records in my query, this new column will show 1,2,3,4,5 in each field from the top down.

Does anyone know how to accomplish this? Your help is greatly appreciated. Thank you.

This site will give you a couple variations...
http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html

 

[bmcgree1]

Thank you, but I tried the second option suggested on the site you gave me but my new field only returns 0's. I have a PaymentID and a LoanID field that I said to count whenever they're not the same (which they never are) but for some reason it's not working. Below is the SQL I added into my query, does anyone know what I'm doing wrong??


(SELECT COUNT(*) FROM tbl_Payment WHERE tbl_Payment.PaymentID > tbl_Payment.LoanID OR (tbl_Payment.PaymentID = tbl_Payment.LoanID AND tbl_Payment.PaymentID >= tbl_Payment.LoanID)) AS Rank

 

[bmcgree1]

Well I didn't figure it out in my query, but ultimately my query is going to be attached at a Report and the is a REALLY simple way to do this in a Report.

See the website below and the word document inside the Access 2000 zip file to anyone who's interested, apparently it also goes into detail about how to do it inside your query as well if you need it, i'm going to glance at that later.

To do it in a Report: Put an unbound Text Box on the Report and go to the Data tab in the Property Sheet. Set the Control Source to =1 and change the Running Sum to Over All. Thats all!

... Thank you Roger Carlson

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=309