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

Posted on 2011-04-28
Last Modified: 2012-06-27
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

Question by:wlwebb
    LVL 77

    Accepted Solution

    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.

    Author Comment

    I get a "Enter Parameter Value" for Job

    Author Closing Comment

    Never Mind.  My field was named CustomerJob instead of just Job.  Thanks got it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now