Identifying most recent entries to include in a report

[Leonard Priestley]

I am having trouble selecting a number of entries from a table, to include
in a report. The criterion is that the date is the last which occurs before
the end of the month and which relates to a particular job. I assume I need
to use a domain aggregate function to do this, but I can't see how to make
it work.

I have written a small database which tracks the points at which repair jobs
get stalled. The idea is to present a report at the end of each month,
showing which jobs are still not complete and what the holdup is. For
example the job may be waiting for parts, or has been sent to an outside
contractor and not yet received back.

The form used shows the data identifying the items being repaired, and there
is a subform which gives dates on which each job has been reviewed and found
to have a new status. For example, on 9th October repair number 27 may be
held up by lack of parts, and by 28th October, the parts may have been
received, but repair 27 is still in process.

On 31st October, I want to identify the last entry for October (i.e. the one
on the 28th) and include that in my report, with the latest entries for all
the other repairs.

Any help will be most welcome

Leonard Priestley

 

[Guest]

I recommend you create a query (qry_MostRecent) that looks something like the
following. I'll assume you have an LogDate field which is used to identify
datetime values when an entry is made in the repair log. I'm also going to
assume that you have a Repairs and RepairDetails tables and that the Repairs
table has a Yes/No field called Completed, which gets checked when the job is
completed.

SELECT RepairDetails.*
FROM RepairDetails
INNER JOIN Repairs
ON Repairs.RepairNumber = RepairDetails.RepairNumber
GROUP By [RepairNumber]
WHERE Repairs.Completed = False
AND RepairDetails.LogDate = DMAX("LogDate", "RepairDetails", "RepairNumber =
" & Repairs.RepairNumber)

HTH
Dale