Extracting a list of records with the latest date and time from a recordset

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access 97
I have a table that holds a number of job records.
Each job (record) has a status change Date and Time.
For each job there could be a number of dates and a number of times for each
date - a separate record for each..

Is there a way I can extract from this table each job with only the latest
date and time - without duplicates?
I am trying to do so in Query Design using Max etc but always end up with
duplicated jobs with different dates/times.

Any help gratefully appreciated.

WSF
 
WSF,

I think the easiest way would be to combine the date and time field
together, which would then allow you to find the most recent status
change. Assuming your date and time are both in Date/Time data type
fields, that is. In the query design window, place the Job field in the
first column of the query design grid, and then in the field row of the
next column, put the equivalent of...
WhenChanged: [NameOfYourDateField]+[NameOfYourTimeField]
Then, make this a Totals Query (slect Totals from the View menu), leave
GroupBy in the Totals row of the Job column in the grid, and Max in the
WhenChanged column. Does this do what you want?
 
Back
Top