Select Only Newest/Most Current date

[Thorson]

I currently have a query-qryIndIDbyDisposition3 set up to show all the
animals that were transferred from one unit to another. Animals are often
Transferred multiple times to different units. Currently the query brings up
the animal every time it is transferred (if the animal is transferred 5 times
it shows up 5 times in the query). I would like the animal to only show up
in the query-qryIndIDbyDisposition3 1 time and display the most recent
transfer information. The query pulls from another
query-qryIndIDbyDisposition (which pulls from a table).

The query-qryIndIDby Disposition3 lists the ear tag of the animal, the
record # of the transfer request, the date of the transfer, the owner,
current location, new owner and new location the fields are titled:
EarTag
Record#
Date
Owner
AtUnit
NewOwner
NewUnit/Location

I would like the "date" field to display the latest/most recent/newest date
for each individual animal.

 

[Michel Walsh]

Make two queries, the first one pumping the MAX( date ) for each EarTag:


SELECT earTag, MAX(date) As latestOne
FROM tablerName
GROUP BY eartTag


and then, make a second query with your original table and the previous
query, join on the earTag and also on date and latestOne fields.


Vanderghast, Access MVP

 

[Thorson]

So I got it to work with the first query, however when I create the second
query I add both "LatestOne" and "EarTag" as fields but as soon as I add the
"DispRecordID" field it brings up all the different dates for each individual
animal again. I'm assuming this is because each Date corresponds to a
different DispRecordID. Do you have a solution for this? I can keep trying
different things.

Thanks for your help!

 

[Thorson]

So another note on this last part is that the DispRecordID Numbers are in
consecutive order, therefore I tried limiting it to the max record number for
each individual EarTag. This works in the first query, but then when I pull
it into the second query along with the "LatestOne" date, it again adds all
entries of transfers for each animal...

 

[Michel Walsh]

You probably have not ''completed'' the join. In the upper part, drag the
LatestOne field, form the query, and drop it over the date field of the
table. Sure, the two 'eartag' fields must also be part of the join (shown
with a line between the table and the first query). So you should end with
TWO lines between the table and the query, in the upper part of the second
query.

Vanderghast, Access MVP

 

[Thorson]

Works Perfectly! Sorry, for some reason I missed the two lines/two joins part.

Thanks!