Only show first X hits for a column in query?
in [Help]
|
Prev: "This file might not be safe if it contains code..." message with runtime version.
Next: Capture field of certain records to display in code as comma separated text
From: Thomas Andersson on 8 Aug 2010 15:37 I have a query that checks various conditions from tables and then output a list of names, what I want is for each name to max show up 2 times. Is this possible and if so how? Table 1 stores the names Table 2 links to 1 and stores personas (each name is related to 3 personas). Table 2 stores all data for each persona and is what is queried. Each of the 3 personas can give multipple hits so in the end the query gives me a list where each name shows 3*x times and I only want each name to show 1-2 times (each member can be credited for a task max 2 times per day but any persona can perform it). Is this clear? Any solution?
From: Salad on 8 Aug 2010 15:54 Thomas Andersson wrote: > I have a query that checks various conditions from tables and then output a > list of names, what I want is for each name to max show up 2 times. Is this > possible and if so how? > > Table 1 stores the names > Table 2 links to 1 and stores personas (each name is related to 3 personas). > Table 2 stores all data for each persona and is what is queried. > > Each of the 3 personas can give multipple hits so in the end the query gives > me a list where each name shows 3*x times and I only want each name to show > 1-2 times (each member can be credited for a task max 2 times per day but > any persona can perform it). > > Is this clear? > Any solution? > This is off the top of my head. I might have 2 or 3 queries. The first query would link Table1 and Table2. Maybe the name of the person and the ID from Table2 would be columns. The second query would get a record counter count of the resutling set of names. Joe might be 5; Sally 1, Mike 3. Select NameFld, Table2ID, _ Dcount("Table2IDz","Table2","Table2ID = " & [Table2ID]) _ As NameCnt From Query1 The third query, probably could be combined in Query2 in the where clause, but I'm keeping separate Select * From Query2 Where NameCnt <= 2 If going to a report you don't need to do this. You can use nextrecord, printlayout, movesection methods/properties to accomplish the same.
From: Thomas Andersson on 8 Aug 2010 17:14 Salad wrote: > I might have 2 or 3 queries. 2 solved it :) Only used table 2 and 2 in the first one and in teh second I did a count on previous result compared to names from table 1. Well, it doesn't give me the top 2 hits for each as I wanted, but it does give me a count of hits at least. Shame I can't use it to pull up the results from severall similar scripts to show results side by side, but adding anotehr only shows a result if there are hits in both :/
From: John Spencer on 9 Aug 2010 09:18 You might solve this with a subquery in your where clause. What that would be is hard to decide given the limited information you have given us about your table structure. For instance, what are the primary key fields in Table1 and Table2? What type of field is the column you want to use to filter the results to 2 records (at most)? Is there a field that has the date and time in table2? Is there a field for the task and if so what is the field type? John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Thomas Andersson wrote: > I have a query that checks various conditions from tables and then output a > list of names, what I want is for each name to max show up 2 times. Is this > possible and if so how? > > Table 1 stores the names > Table 2 links to 1 and stores personas (each name is related to 3 personas). > Table 2 stores all data for each persona and is what is queried. > > Each of the 3 personas can give multipple hits so in the end the query gives > me a list where each name shows 3*x times and I only want each name to show > 1-2 times (each member can be credited for a task max 2 times per day but > any persona can perform it). > > Is this clear? > Any solution? > >
From: Thomas Andersson on 10 Aug 2010 01:26
John Spencer wrote: >> I have a query that checks various conditions from tables and then >> output a list of names, what I want is for each name to max show up >> 2 times. Is this possible and if so how? >> >> Table 1 stores the names >> Table 2 links to 1 and stores personas (each name is related to 3 >> personas). Table 2 stores all data for each persona and is what is >> queried. Each of the 3 personas can give multipple hits so in the end the >> query gives me a list where each name shows 3*x times and I only >> want each name to show 1-2 times (each member can be credited for a >> task max 2 times per day but any persona can perform it). >> >> Is this clear? >> Any solution? > You might solve this with a subquery in your where clause. > > What that would be is hard to decide given the limited information > you have given us about your table structure. > > For instance, what are the primary key fields in Table1 and Table2? > What type of field is the column you want to use to filter the > results to 2 records (at most)? > Is there a field that has the date and time in table2? Is there a > field for the task and if so what is the field type? Ok, see if I can clear things out. In Table 1 membername is primary. In table 2 membername is non-unique and linked to table 1. Primary key in table 2 is a persona id (each member have 3 persona). Table 3 contains all data on sorties that I query, it's linked to Table 2 via the persona id and primary is a sortie id. So, search all sorties for matches, each match linked to either of 3 personas in turn linked to a single name. Query only shows this member name so result is a long list of names each occuring X times, and I only want 1 or 2 from each. Better? Right now I've managed so the result I get is a list with each name listed once and the next column a count of hits for him, close enough I guess. |