Make table

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

HI.
I need to create a rank field. Currently I run a make
table and add go into the table and add a field called
rank and make it an auto number to generate the rank. Is
their a way I can automate this?

The data would look like this:

I need to add the rank field and the rank is based off of
the descending order of the count of the contact ID field.
example:

Rank ID Contact ID
1 6222 10
2 7222 8
3 3111 2
4 1111 1
 
Try using a correlated subquery such as the following as the field source for
your Rank Field:

Rank: (SELECT Count(*) FROM YourTable as VT WHERE VT.[Contact ID] >
YourTable.[Contact ID])+1

Just replace YourTable with the name of the table that your query is drawing
the data from, and change the Contact ID field name if it is slightly
different.

HTH, Ted Allen
 
Back
Top