Jump to content
Sign in to follow this  
seraph_nyc

Top Ten lists

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

Top ten how? What is the rating scale?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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