Select first three records

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

Guest

I'm sure there is a really simple solution to this but I haven't the foggiest
on how to do it. I have a table with airplane deliveries and would like to
write a query that returns only the first three airplanes deliveries for each
operator.

I only have one table with fields such as: DelivDate, Operator, ID,
OprCountry. This table has thousands of records for multiple operators and
in some instances some operators have only had two airplanes delivered.

The first three airplane deliveries would be based on the DelivDate

Thank you in advance for my question.

Tina
 
Thank you SOOOOOO much!

The only quirk I have in the Method 2 is that in some instances there are
two airplanes delivered on the same day. When I run the query with the
module built as in Method 2, I get too many records for these days. I have
it set to pull the the first three records, but I sometimes get 4 or 5
records for these cases. Can you suggest anything for these?
 
Yes, if Access finds a tie, it does return more than the 3 records.

The trick is to give it some way to decide which records to return. A simple
way to do that is to add the primary key to the end of the ORDER BY clause.

Presumably you created the query, and set its Top Values property to 3. Drag
the primary key into the field to the right of any existing fields. Choose
Ascending in the Sorting row under this field, and uncheck the Show Box.

If you switch the query to SQL View (View menu), you will see this kind of
thing:
SELECT TOP 3 ...
FROM ...
WHERE ...
ORDER BY FlightDate, ID;

Since the ID field is unique for every record, it is now able to figure out
which ones are the TOP 3 when there are records for the same date.
 
Back
Top