Sorting and reporting by Frequency

[rexproctor]

Hi All,

This is my second post. This forum has proven an invaluable resource to the newbie Rex. Thanks to all that manage it and post.

I am trying sort and create a report, records by the frequency of values descending from the most to the least occurances in a given field.

Explanation:

I have 70 records that have 20 fields describing them. I would like to sort the 70 records by one field according to the frequency that the values occur in the records. Much like the alphabetical descending sorting, but by frequency of the value occurance. I would like this to appear in a list.

Example:

1. Bannana----------------->1. Kiwi

2. Apple------------------->2. Kiwi

3. Apple-----Sort----------->3. Kiwi

4. Orange----Kiwi is first----->4. Kiwi

5. Orange ------------------>5. Orange

6. Orange-----It is the most--->6. Orange

7. Kiwi--------frequent value-->7. Orange

8. Kiwi----------------------->8. Apple

9. Kiwi----------------------->9. Apple

10. Kiwi---------------------->10. Bannana

I hope the explanation is not too confusing. Any thoughts would be appreciated.

Regards,

Rex

[RalphL]

Start by making a self relationship, i.e. a relationship to itself usinf the field fruit as the key field on both sides of the relatioship.

Add a calculated number field Count = Count (Self::Fruit).

Sort on this field (Count) in desending order.

[rexproctor]

Thanks for the repsonse Ralph. I have tried thism but the count only returns one because each record has only one fruit. How can I have the count look at all 10 records and return 4 for Kiwi?

[RalphL]

Take a look at the sample file attached.

[rexproctor]

I go it Ralph! I set the count to look at the new self-relating table. This is to ******* simple.

Thanks a lot,

Rex