Solved Show Last Record in Query by Date (1 Viewer)

[theinviter]

Dears;
Need help,
How to filter the query to show only last record by date, as the filed name DATE_.

thanks

 

[arnelgp]

you create another query (qry1) that will retrieve PK number and the Max of date:

select [PK field], Max([yourDateField]) As MaxOfDate from yourTable;

now join this qry to your Original query and join them on the two fields.

 

[XPS35]

Can be done 1 query.

SELECT TOP 1 * FROM YourTable ORDER BY DATE_ DESC

Even arnelgp's solution can be done In one query using a sub-query.

 

[arnelgp]

Even arnelgp's solution can be done In one query using a sub-query

your query will return only 1 record?
what if there are many PKs/Date on the table?

 

[XPS35]

your query will return only 1 record?
what if there are many PKs/Date on the table?

Then only one record will be shown.
The wish was "only last record by date". So 1 record.

Edit: If the time parts of a same date differ, your query will also select 1 record.

 

[Pat Hartman]

Sorry, but why would you ever name a field "DATE_"? You were right to not use Date because Date is the name of a function and as you probably discovered, you had trouble with a field named Date in VBA. But maybe you could use SaleDate or something more descriptive than a trailing underscore.

Simple is better. Use the Top 1 query.