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?

Thomas Andersson wrote:


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.

Salad wrote:

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 :/

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:

John Spencer wrote:

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.

Thomas Andersson wrote:
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.

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: