• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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.


DLast([tblJCJobStatusChanges]![ChangeMonthEnd],[tblJCJobStatusChanges],[ChangeMonthEnd])

Open in new window

0
wlwebb
Asked:
wlwebb
  • 2
1 Solution
 
peter57rCommented:
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.
0
 
wlwebbAuthor Commented:
I get a "Enter Parameter Value" for Job
0
 
wlwebbAuthor Commented:
Never Mind.  My field was named CustomerJob instead of just Job.  Thanks got it.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now