lanceomni Posted September 22, 2007 Share Posted September 22, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Ender Posted September 22, 2007 Share Posted September 22, 2007 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 More sharing options...
Recommended Posts
This topic is 6235 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 accountSign in
Already have an account? Sign in here.
Sign In Now