Access - DLast to get the most current status change for each Job

Hello all.  

Excuse my stupid question but I have never used DLast.  It may not even be the correct function to use for what I am attempting and if it is not, please chime in with better ways to do what I am attempting.

I have a list of Jobs and for each of those jobs I have to track a history of changes to their status.  The statuses are either: Open, Closed, Reopened or Reclosed.

My table has fields for a) Job Name; b) Date of Status Change; and c) Status.

I need to create a query table that shows just the LAST status of every job

In my query design I have
Field - ChangeMonthEnd
Table - tblJCJobStatusChange
Sort - {Blank}
Show - Checked
Criteria - see code

When I "Run" it I get "Enter Parameter Value".  Guess I just don't understand the syntax "criteria" statement.


Open in new window

Who is Participating?
peter57rConnect With a Mentor Commented:
To get the latest status for each job you need two steps. First you have to get the latest changedate for each job and then you have to get the staus that matches the job and changedate.
Expressed as one query you get....

Select * from tblJCJobStatusChanges as tblA  inner join
(Select Job, max(ChangeMonthEnd) as Maxdate from tblJCJobStatusChanges
Group By Job) as q1
On tblA.Job = q1.Job and tblA.ChangeMonthEnd = q1.maxdate

You can save the inner query first if you wish and then create a new query from the original table and the saved query.
wlwebbAuthor Commented:
I get a "Enter Parameter Value" for Job
wlwebbAuthor Commented:
Never Mind.  My field was named CustomerJob instead of just Job.  Thanks got it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.