Getting the lastest data from a table

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

WSF

Access 97
I have a table [tblLastTechEvent_1] comprised fields

JobNo This is the Job No
StatusChangeInitials This is the technician's initials
StatusChangeTime This is a time field recording each status change time
Status This is the Job Status at that time

For a given date the table holds jobs numbers against which there is listed
the technician, job status and the time of that status. Job numbers and
technician's initials can be duplicated many times, because each time the
status of the job is changed a record is made (accumulated) in this table of
when (time) a change was made and by whom (Tech).

I am trying to query the table by asking "please return the latest time a
tech made a status change, and what was the job number and status of that
change.

I can extract the latest time per tech using the following (which is crude
copy directly from the query design (SQL view):

SELECT tblLastTechEvent_1.StatusChangeInitials,
Max(tblLastTechEvent_1.StatusChangeTime) AS MaxOfStatusChangeTime
FROM tblLastTechEvent_1
GROUP BY tblLastTechEvent_1.StatusChangeInitials
ORDER BY tblLastTechEvent_1.StatusChangeInitials,
Max(tblLastTechEvent_1.StatusChangeTime);

but I cannot get the job number and status at all.
When I add reference to them to the query, instead of say 20 records, I get
200 (or the whole table).

It does not have to be via a query design I guess, but any SQL equivalent
code would be appreciated.

Any help gratefully appreciated.

WSF
 
Back
Top