Probs Selecting Top Value Fields

[mikebaldam]

I'm trying to get a query to work on a table pulling down all information in the table only by the latest dates for each AddressID (which is a foregin key from TblAddress).

I'm having trouble using the TOPVALUE option as this will only display the information for the TOPVALUE of AddressID and not for each AdderssID's Date field.

Is there another way of getting this information

I think I may be going about this all wrong..... so any advice would be greatfully apppreciated...

Thanks

Mike

 

[Nouba]

This query shows one way to accomplish the goal.

SELECT
  T.ADDRESSID
, T.DATEFIELD
FROM YourTable AS T
WHERE T.DATEFIELD = (
  SELECT
    Max(DATEFIELD)
  FROM YourTable
  WHERE ADDRESSID = T.ADDRESSID)

 

[mikebaldam]

Works a treat



Cheers


Mike