selecting records based on the position in the database

  • Thread starter Thread starter Jo
  • Start date Start date
J

Jo

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
 
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
 
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
 
Hi,




SELECT LAST(a.Reference),
LAST(a.Amount),
a.dateTime,
COUNT(*) as rank,
1+(COUNT(*)-1)\15 as theGroup

FROM myTable As a INNER JOIN myTable As b
ON a.dateTime <= b.dateTime

GROUP BY a.dateTime



would supply you, in the computed field "theGroup", with a value denoting
the group in which the record belong.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top