Sorting largest # of recurrences

[lanceomni]

I am trying to figure out the best way to to sort records (from high to low) based on the amount of times the same information was used for a field. Say I had a field called "Name" which can re-occur

and my database contained thousands of records like this

John

Jamie

John

Jamie

Justin

Peter

George

John

Sam

Sam

John

Justin

Amanda

Tim

Bert

and I want them in this order

John

John

John

John

Jamie

Jamie

Justin

Justin

Sam

Sam

Amanda

Bert

George

Peter

Tim

Or even better yet like this so I can sort them by number of recurrences.

1 Amanda

1 Bert

1 George

2 Jamie

4 John

2 Justin

1 Peter

2 Sam

1 Tim

Thanks,

Justin

Edited September 22, 2007 by Guest
removed all of the wasted spaces

[Ender]

Define a self-join relationship based on the Name field, then define a calculation:

Count(self-join::recordID)

and sort by that. If you wish to consolidate the duplicates, use a sub-summary report, with a sub-summary by Name, and stick the Name and Count fields on the part (and remove the Body). You can view the sub-summary parts when the records are sorted by the sub-summary break fields and the layout is viewed in Preview Mode or printed.

For a large table, the sorting of that unstored calc might be kind of slow. You may have better performance if you use a regular number field and use a script to set the counts in all records.