Re: selecting records based on the position in the database

From: Jo (anonymous_at_discussions.microsoft.com)
Date: 09/29/04 

Date: Tue, 28 Sep 2004 23:38:38 -0700

Thank you for your help.

The top values won't work because i need to retrieve all
records in groups of 15. (there are very many records).

Unfortunatley i am a novice in the query writing dept and
wonder if you could expand further with regards
to "ranking" my records

The table is called a Records with the following relevant
fields; i have added the date/time field as recommended
thanks -

Date/Time
Reference
Amount

I am using Access 97.

Hope you don't mind helping further

Thanks

>-----Original Message-----
>Hi,
>
>
>You have a date time stamp field?
>
>
>SELECT TOP 15 *
>FROM myTable
>ORDER BY dateTime DESC;
>
>
> supply the latest 15. Otherwise, rank your records:
>
>
>SELECT LAST(a.f1), LAST(a.f2), LAST(a.f3), ... , LAST
(a.fn), a.dateTime
>FROM myTable As a INNER JOIN myTable As b
> ON a.dateTime <= b.dateTime
>GROUP BY a.dateTime
>HAVING COUNT(*) BETWEEN 46 AND 60
>
>
>as example.
>
>That assumes you don't have two records with the same
dateTime value, and no
>null values, ie. that dateTime can be a primary key.
>
>
>Hoping it may help,
>Vanderghast, Access MVP
>
>
>
>"Jo" <anonymous@discussions.microsoft.com> wrote in
message
>news:030e01c4a596$c357dfd0$a401280a@phx.gbl...
>>I wonder if someone can help me please.
>>
>> I need to write a query for a database that needs to
>> produce regular reports based on the latest records in
the
>> database (records are frequently added and deleted and
>> therefore the query cannot be based on the ID). The
>> records are to be grouped 15 at a time. The database
will
>> be operated by someone other than myself and therefore
>> needs to do the process automatically. Quite simply i
just
>> want to select the first 15 records, the next 15 and so
on.
>> Can anyone help please
>
>
>.
>

Relevant Pages

  • Re: selecting records based on the position in the database
    ... You have a date time stamp field? ... That assumes you don't have two records with the same dateTime value, ... > I need to write a query for a database that needs to ... > therefore the query cannot be based on the ID). ...
    (microsoft.public.access.queries)
  • RE: Select across databases
    ... > How would I add this to the query? ... > also in the other database. ... > DECLARE @startdate AS DATETIME ...
    (microsoft.public.sqlserver.programming)
  • Re: DateTime.Now function question!
    ... some reason in my query the datetime comes up as comma seperated ... Like dateRequest = 39141,2890187037 ... What is the type of the column in the database? ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: DateTime.Now function question!
    ... some reason in my query the datetime comes up as comma seperated ... Like dateRequest = 39141,2890187037 ... How are you storing the data in the database? ...
    (microsoft.public.dotnet.languages.csharp)
  • Database query performance
    ... I have a database table. ... another is DateTime. ... The problem is that this query is very slow. ... Prev by Date: ...
    (microsoft.public.dotnet.framework.adonet)
Loading