Lady in distress

[Jilly]

Hi there

I have a database which is for recording inspection criteria...code, lot
number, result, date reconciled. In order for the system to report if a code
is to have a lowered inspection criteria i have to examine the last 10 code
entries on the database but at the moment i cant find a way of getting this
information easily. There is just one slight problem. I have multiple entries
of the same code which I need the top 10 for each code. Your solution would
only give me the top 10 of the whole query...dont suppose you have any
suggestions tips

Can anyone help me please!!

 

[Nikos Yannacopoulos]

Jilly,

To begin with, sort your query on Date Reconciled, descending to get the
lastest at the top; then click in the grey area on the upper half (above the
grid, where the tables are) to select the query object, and right-click and
select properties. Set the Top Values property to 10.
Of course, this will only work for one code at a time. making it work for
all codes at once gets trickier. It could probably be done with subquries,
which I haven't tried, or it could be done with some VBA code to populate a
temporary table with the top 10 per code.

HTH,
Nikos

 

[Duane Hookom]

Try something like:
SELECT *
FROM tblInsp
WHERE DateReconciled in
(SELECT TOP 10 DateReconciled
FROM tblInsp I
WHERE tblInsp.Code = I.Code
ORDER BY DateReconciled DESC);