Re: Display only the 25th record
- From: "Chip" <Chip@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 7 Feb 2006 15:08:27 -0800
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.
"Michel Walsh" wrote:
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
"Chip" <Chip@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E23602B-49B2-43F6-9041-3DF11D7E75DD@xxxxxxxxxxxxxxxx
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.
"John Spencer" wrote:
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.
"Chip" <Chip@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3FFD3F42-F19C-439E-A3D3-5BFCBFF6A493@xxxxxxxxxxxxxxxx
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?
- References:
- Re: Display only the 25th record
- From: John Spencer
- Re: Display only the 25th record
- From: Michel Walsh
- Re: Display only the 25th record
- Prev by Date: Re: Query in VBA Type Mismatch
- Next by Date: Re: KB WRONG, for TOP N values per group query.
- Previous by thread: Re: Display only the 25th record
- Next by thread: Incorrect Record Count - Perhaps Corruption?
- Index(es):
Relevant Pages
|
