query to return the newest

[stuartam]

i have a table with the following fields:

IDNUM - PACKREF - STATEHISTORY - USERHISTORY- DATEHISTORY

i have a query that returns all the data with a matching 'PACKREF'

but i want it to only display the one with the newest date ( date in the 'DATEHISTORY' field )

how can i do this?

best regards

 

[daveUK]

In the query design window, select the Sigma sign on the toolbar at the top of the screen - it looks a bit like a capital 'E'. This will add another criteria to the query grid called 'Total' (it should be underneath 'Field' and 'Table'). You can then select 'Last' from the drop-down menu next to 'Total' and it will only disply the last record of the table, which should be the record with the newest date.

I hope I've explained this ok.

Dave

 

[stuartam]

thanks for the reply, ive tryed that but it didn't work

it just displays all the data no just the one with the newest date.

maybe ive done something wrong?

regards

 

[sfreeman@co.mer]

Hth

SELECT
IDNUM
, PACKREF
, STATEHISTORY
, USERHISTORY
, DATEHISTORY

FROM
tblRecords

WHERE
(((tblRecords.DATEHISTORY) In (select max(DATEHISTORY) from tblRecords)));

 

[stuartam]

thanks guys, couldent get that too work either ( was probabilly putting it in the wrong place ) but i did get this too work:

(select max(DATEHISTORY) from TBLPackHistory)

thanks for all the help

 

[daveUK]

You need to select 'Last' in all of the fields and it will only display the last record.