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: Salad on 10 Aug 2010 02:19 Thomas Andersson wrote: > 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. > > sortie (military) An offensive military mission. Used originally to mean an attack from a fortress, but most commonly used today to describe a single mission by a military aircraft. Persona, in the word's everyday usage, is a social role or a character played by an actor. Better? I don't know. I didn't see John's questions answered. Maybe a small example set of data and a small dataset of desired output would help. Then again, maybe not.
From: John Spencer on 10 Aug 2010 08:31
OK, still missing some information. (Oh, and now we know that there are three tables involved and not two.) Does the Sorties table have a primary key? Does the Sorties table have a date field? Does it matter which two records are shown from the sorties table for a member per day? If you are using the results in a report you can suppress printing rows of data in the report with a query that returns all the data. It is fairly easy to do and if the data set for the report is not very large the process is usually quick. It might help if you posted the SQL of the query you do have. If you want to do this in the report. Using the Sorting And Grouping of the report(Menu: View: Sorting and Grouping) Group By MemberName Group By Date (if you have multiple dates) Add any other sorting you wish to do Add a control to the detail section Name: txtLineCounter Control Source: =1 Running Sum: Over Group Visible: No Add the fields to the detail section that you wish to print In the Detail section's format event add a little bit of VBA Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.Section(acDetail).Visible = Me.txtLineCounter <= 2 End Sub If you wish to and need to do this in a query, then please supply the additional information requested. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Thomas Andersson wrote: > John Spencer wrote: > SNIP > 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. > > |