Top Ten lists

[seraph_nyc]

Hi all

I am trying to find a way to create top ten lists with either self joins or multi-key relationships. Basically I have a list of questions and I want to see the top ten responses for each.

Edited April 18, 2007 by Guest

[mr_vodka]

Top ten how? What is the rating scale?

[seraph_nyc]

Well lets say there is a category for favorite celebrity. And there are 1000 records. It will look at all the records and find the top ten responses. I think this is basically a duplicate finder which stores its searches and only selects the top ten. So I need to make this so it works in a portal rather than a separate self join.

[mr_vodka]

Create a self join keyed on QuestionID and Answer. Then create a calc field that is a Count (SelfJoin::Answer)

Then your portal can be sorted by this calc field in Descending order, Have the portal only have 10 rows.

[seraph_nyc]

hmmm it isn't quite working

I have a field called answer. and then I have a field called count which counts all the times an answer is there. But when I do the portal it only show the current answer. I added the file. I think I have the concept but I am missing something small.

top_ten_lists.zip

[mr_vodka]

I dont understand what you are trying to accomplish here. Why are you trying to show the to 10 answers from an layout of individual answers?? I was under the impression that you had a questions table and a related answers table. Then I was assuming that you wanted to shot the top ten answers that were used for a particular question.

Also should this not be in a summary report instead or a portal

[seraph_nyc]

Yes, I have ten questions, 1000 records. I want to know the top ten responses of each question. I only included one question in this version of the database to keep it simple. I got all the counts working, I just need a way of listing them in order. In fact, if I can use a portal I dont need to limit it to top ten.

[mr_vodka]

Well in your sample, you have 50 questions as per your Question serial number being 50 records.

Again I think this would be better served with a summary report. Why do you want this in a portal?

[seraph_nyc]

Ok how would I do that in a summary report? I like the idea of portals since it can be update with portal filtering (which isn't part of the sample). I like multi joins. But if I need to do a summary then I will. is there a good resource for this?

[mr_vodka]

I have attached your file back with some modifications. Both methods have been included. Using a summary report is the preferred method because it is more "natural". The other way through a portal requires a global field to be set.

top_ten_revised.zip

[seraph_nyc]

I am having a bit of trouble understand this. It seems that first is counts all the responses. then it create a record for each count and then displays the portal with each count. Is that correct?

[mr_vodka]

There is no record creating going on here. The self join is used to count all the responses. The TO of celebrity is used to display all the related celebrity names. The additional TO of answers is to show the calc that was used to count earlier.

My suggestion would be to read up on Table occurences, TOG, etc.

Also as I have said numerous times now, the more conventional way, and the way that I think you should go about it is to use a summary report since it seems more natural. Just my 2 cents.

[seraph_nyc]

Hi, this is for a marketing database and we are tracking occurrences over a range of people. We like the "real time" aspect that I made with multi joins and portal filters.

[seraph_nyc]

I am able to understand how to get counts of each record. But when I do my own portals it only displays the count of the for each one seperately. I just can wrap my head around it. I re did the database removing extra stuff I have put in to randomly create answers.

top_10_Copy.zip

[mr_vodka]

My sample file and your file looks vastly different. As I suggested, it may be good for you to read up on table occurences. The white paper that is linked in my signature is a decent start.