how to keep only the most current date record in Table (1 Viewer)

[ttomw]

I have this table that records sales events for properties, with multiple sales records for some parcels. Each parcel has unique field: MapTaxlot. I want to create a select query or make table query that shows only the most recent sale event for each parcel. Instrument_Date is the date field for the sales records.

See attached example of the data table below.

What is the simplest method to accomplish this?
Thanks,

 

[pbaldy]

How about

http://www.baldyweb.com/LastValue.htm

 

[ttomw]

Here is what I ended up with:
SELECT Max(Sales2Acre.Instrument_Date) AS MaxDate, Sales2Acre.MapTaxlot
FROM Sales2Acre
GROUP BY Sales2Acre.MapTaxlot;

SELECT Sales2Acre.*
FROM Sales2Acre INNER JOIN qryMaxDates
ON (Sales2Acre.MapTaxlot = qryMaxDates.MapTaxlot)
AND (Sales2Acre.Instrument_Date = qryMaxDates.MaxDate) ;

That method worked to eliminate all records older than the MaxDate but where there were multiple records for a given "Maptaxlot" with the same Maxdate they were all included.

Is there a way to eliminate all duplicate records for the same Maptaxlot and Maxdate as part of this process, or do I need to just go through the copy paste method of creating a new table to get unique Maptaxlot records?

Thanks!

 

[pbaldy]

Surprising a property could sell twice in one day. Given that, I'd probably have included the time. In any case, is there an ID field that can be included?

 

[ttomw]

Surprising a property could sell twice in one day. Given that, I'd probably have included the time. In any case, is there an ID field that can be included?

Yes I also don't understand how multiple transactions can occur for the same parcel on the same day but that is what the data is.

You mean a unique sales ID for each transaction? Unfortunately no.

 

[ttomw]

I could edit the Sales2Acre Table to add an autonumber field...