assign a value to each record in a query

T

Tracy

I have a query that stems from a table with an autonumber field already
assigned. One of the fields named 'bp_order' is a number. I need to assign
a record number to each record matching bp_order in descending order.

For instance,
bp_order 3200 should = 1
bp_order 2800 should = 2

and so on. As I said, I already have an autonum in the table so that won't
work. I need to dynamically write an expression where it'll basically number
my records, according to bp_order descending. Does anyone have a clue?

Thanks,

TM
 
M

MGFoster

Tracy said:
I have a query that stems from a table with an autonumber field already
assigned. One of the fields named 'bp_order' is a number. I need to assign
a record number to each record matching bp_order in descending order.

For instance,
bp_order 3200 should = 1
bp_order 2800 should = 2

and so on. As I said, I already have an autonum in the table so that won't
work. I need to dynamically write an expression where it'll basically number
my records, according to bp_order descending. Does anyone have a clue?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Possibly this (untested):

SELECT (Select Count (*) from <table name> Where bp_order >
T1.bp_order+1) As RecNo, bp_order
FROM <table name> As T1
.... etc. ...
ORDER BY bp_order DESC -- remove this if RecNos are reversed.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyiod4echKqOuFEgEQImjQCg72SfTV28injvYeYEZalhZRw/YdIAoPpv
xM1KOjfVSQrbEDpIgYYaDQl/
=aLRx
-----END PGP SIGNATURE-----
 
Top