select top

[Striker]

What is the best way to select the top 5 records from a field configured as
DATE/TIME in Access 2007? I know I can select * and order by, to get the
last records. I only need the last 5 by the date they were added to the
table.

Thanks

 

[Dirk Goldgar]

What is the best way to select the top 5 records from a field configured
as DATE/TIME in Access 2007? I know I can select * and order by, to get
the last records. I only need the last 5 by the date they were added to
the table.

If I understand you correctl, the query would be along these lines:

SELECT TOP 5 * FROM [MyTable]
ORDER BY [MyDateTimeField] DESC

 

[Striker]

OK, so it's the same as SQL. I was doing that as a select top 10. it kept
returning 16 records. I did notice some of the dates were the same on the
records however.

What is the best way to select the top 5 records from a field configured
as DATE/TIME in Access 2007? I know I can select * and order by, to get
the last records. I only need the last 5 by the date they were added to
the table.

If I understand you correctl, the query would be along these lines:

SELECT TOP 5 * FROM [MyTable]
ORDER BY [MyDateTimeField] DESC

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OK, so it's the same as SQL. I was doing that as a select top 10. it
kept returning 16 records. I did notice some of the dates were the same
on the records however.

The TOP predicate makes no attempt to distinguish among records that have
the same value in the ORDER BY field(s). It returns them all. If you need
to return exactly 5 (or whatever), you can include the table's primary key
as a second ORDER BY field. Of course, then you have to understand that
your "top 5" aren't really an exclusive set.