Calculate number of smaller records in field

[Guest]

I am trying to write a query that will tell me how many records in a given field are (i) smaller than and (ii) larger than each record in that field. This is similar to a ranking function. I can accomplish this in Excel, but I can't seem to do it in Access

In other words, if I have a field with 1,000 records, I would like to know how many records in that field are smaller and larger than records 1,2,3...1,000.

 

[ChrisJ]

Smaller: DCount("[YourField]","[YourTable]","[YourField]>"&
[YourField])
Larger: DCount("[YourField]","[YourTable]","[YourField]<"&
[YourField])

-----Original Message-----
I am trying to write a query that will tell me how many

records in a given field are (i) smaller than and (ii)
larger than each record in that field. This is similar to
a ranking function. I can accomplish this in Excel, but I
can't seem to do it in Access.

In other words, if I have a field with 1,000 records, I

would like to know how many records in that field are
smaller and larger than records 1,2,3...1,000.

 

[Guest]

Thanks, Chris. That is a big help.