Need query to only give last date of record

[techjules]

I need help with a database in Access -- here is a sample of a set of data:

PARCEL_NO LastOfSALE_DATE SALE_AMT OWNER NAME
0120295MF00000030 2/20/2009 100 WESLEY M G & AMY M
0120295MF00000030 1/19/1997 60000 WESLEY M G & AMY M
0120295MF00000030 6/19/1997 332600 WESLEY M G & AMY M
0120295MF00000030 6/20/2001 350000 WESLEY M G & AMY M
0120295MF00000030 6/19/1996 9301000 WESLEY M G & AMY M

I need a query that will only return the last date and corresponding sale amt. For the example above, I want the query to return the record with 2/20/2009 with $100 for the amount.

I have been using the Total and group by "Last" but I am not getting the result I want.

Any advice?

 

[jrbarnett]

Can you post the SQL code please.

John

 

[techjules]

Thank you for responding. I don't have any code -- this was the result of a basic query. I need the query to only return the latest sale date and sale price.

 

[dhookom]

What field is the primary key from your table/recordset?

Duane
Hook'D on Access
MS Access MVP

 

[techjules]

Field 1 is the primary field -- it is a unique parcel number.

 

[techjules]

The records in the database are land records - Field 1 is the parcel number for each address. The database contains all of the historical data for a particular parcel number. I need to isolate only the latest sale date with corresponding sale amount. In the above case, I would want the query to return only the record in 2009 with $100 sale amt.

 

[dhookom]

What are the exact table and field names? Looking at your data, the parcel number can't be the primary key since every record you are showing has the same value.

BTW, you should consider using the Pre TGML tag to format your data so it is readable. For instance:

[pre]PARCEL_NO LastOfSALE_DATE SALE_AMT OWNER NAME
0120295MF00000030 2/20/2009 100 WESLEY M G & AMY M
0120295MF00000030 1/19/1997 60000 WESLEY M G & AMY M[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[techjules]

The field names ARE the names shown in your better formatted example. You're right - the parcel number isn't really a primary in the sense that each record has a unique identifier. I view the parcel number as kind of being a primary because it is unique to a particular parcel of land. I need each parcel of land to only show once with the latest sale date and corresponding amount. I am not following why having another field of a unique primary number would do anything. If so, then let's say each row is numbered 1, 2, 3, 4, 5

 

[dhookom]

No table name? No actual primary key? Assuming a table name of TechJules and primary key of RecID try:

SELECT *
FROM TechJules
WHERE RecID = 
(SELECT TOP 1 RecID
 FROM TechJules T
 WHERE T.Parcel_No = TechJules.Parcel_No
 ORDER BY LastOfSALE_DATE DESC)

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way:

SELECT A.PARCEL_NO, A.LastOfSALE_DATE, A.SALE_AMT, A.[OWNER NAME]
FROM yourTable A INNER JOIN (
SELECT PARCEL_NO, Max(LastOfSALE_DATE) AS MaxDate FROM yourTable GROUP BY PARCEL_NO
) B ON A.PARCEL_NO = B.PARCEL_NO AND A.LastOfSALE_DATE = B.MaxDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[techjules]

Thanks both -- I'll give both examples above a try. DHook - sorry I wasn't understanding what you were wanting ID and table name for -- now I get it