Jump to content

Sorting largest # of recurrences


lanceomni

This topic is 6119 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
removed all of the wasted spaces
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6119 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.