Display only the 25th record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query lists Name, Date, and WordSpoken. For each Name I need to display
only record 25 and record 50.
I tried a report whcih worked but was messy looking with lots of dead space.
Besides, I will probably want to send results to Excel.
Is this even do-able in a query?
 
So what determines record 25 and record 50. - the date, some number field?
Records are not stored in any order so to determine the 25th or 50th record
requires that they be sorted or ranked in some manner.

You could do this in a report by suppressing the detail rows that weren't
record 25 and 50. You would need to group by your Name field
Add a control and set it up as
Control Name: txtLineCount
Control Source: =1
Running Sum: OverGroup

In the On Print event of the detail section, test the value of LineCounter
and see if it is 25 or 50 and set cancel to True for all other lines.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Not (Me.txtLineCount = 25 Or Me.txtLineCount = 50) Then
Cancel = True
End If
End Sub

If you need to do this in a query, then you need to post back with an
explanation of how your records are ordered to determine the 25th and 50th
record.
 
John,
I had done the report the same way you suggested. I really do need a query
though.
The records are sorted by Subject ID and Date. We are trying to find the
date when each subject spoke his 25th, 50, etc. word.
In a separate query I am trying to display the first 25 words (records ) for
each SubjectID.
 
Hi,


I would rank the records, by subject:


SELECT a.subjectID, a.date, COUNT(*)
FROM myTable As a INNER JOIN myTable As b
ON a.subjectID=b.subjectID AND a.date <= b.date
GROUP BY a.subjectID, a.date
HAVING COUNT(*) IN(25, 50)



Indeed,

SELECT a.subjectID, a.date, b.date
FROM myTable As a INNER JOIN myTable As b
ON a.subjectID=b.subjectID AND a.date <= b.date
ORDER BY a.subjectID, a.date, b.date


will return 1 record, for a.subjectID having the minimum a.date value, for
that subject,
will return 2 records for the same subjectID with the next date value, and
so on.
Counting these records supply the rank, by subjectID, ordered by the date
value, ie, first, second, third, ...
You said we have to keep only those having a score of 25 or 50.


If you want the associated data, say, for the first 25 records, by subject:

SELECT a.subjectID, a.date, COUNT(*), LAST(a.otherField1),
LAST(a.otherField2), LAST(a.OtherField3)
FROM myTable As a INNER JOIN myTable As b
ON a.subjectID=b.subjectID AND a.date <= b.date
GROUP BY a.subjectID, a.date
HAVING COUNT(*) <=25





Hoping it may help,
Vanderghast, Access MVP
 
Thanks. I'll try this out. I actually realized that for the first part(show
record 25 and 50 that I could just send my report to Excel. That worked fine
until my client said keep it in Access to use as part of a master data report
we will be using. Thae second part (show the first 25 records for each
subject had me stumped.
 
I start with a query called qryChronologicalUniqueWords that lists
SubjectID, DateSpoken, and WordSpoken sorted by SubjectID and DateSpoken. I
now need to extract the 25th and 50th record for each subject. In a third
query I need to dispaly the first 25 records for each subject.
 
Back
Top