seraph_nyc Posted April 18, 2007 Share Posted April 18, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 Top ten how? What is the rating scale? Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 18, 2007 Author Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 18, 2007 Author Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 18, 2007 Author Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 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? Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 18, 2007 Author Share Posted April 18, 2007 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? Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 19, 2007 Author Share Posted April 19, 2007 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? Link to comment Share on other sites More sharing options...
mr_vodka Posted April 19, 2007 Share Posted April 19, 2007 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. Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 19, 2007 Author Share Posted April 19, 2007 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. Link to comment Share on other sites More sharing options...
seraph_nyc Posted April 19, 2007 Author Share Posted April 19, 2007 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 19, 2007 Share Posted April 19, 2007 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6217 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