Most recent value report

[Guest]

Hi there,

I have two tables: tblOwner and tblHerdInformation.
Fields in report: Municipality, NumberInHerd, TestingCompleteDate

Issue:
I am trying to create a report where by I have a listing, given a specidic
time frame, of municipality and the number of cattle tested for each
municipality. Also included is the number of herds that represents. The
herd count can be determined by the testing date. Each testing date
represents one herd tested.

Problem.

There are several instances where a herd has been tested more than once in a
given time frame. In that case, I want the most recent date and cattle
count so as not to duplicate cattle numbers.

How can I acheive this report?

Kind regards in advance,

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The article explains 4 ways to get the most recent test data.
The subquery might be the simplest.

Another possibility might be to use a subreport to show the testing info.
Base the subreport on a query that sorts in descending date order (so the
most recent record is first), and then in the query properties (Properties
box in query design view), set Top Values to 1 so it contains only the most
recent record for the herd. Then if the main report contains a record for
each herd, the subreport will show just the most recent test info for that
herd.