Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    24

    Question Unanswered: Acc 2003 Problem with Query to return latest record

    Hi

    I have a table (caseStageTran) which stores proceedings of a case in the court.
    caseStageTran
    caseID (FK)
    caseDate
    caseStageID (FK)


    CaseStageID
    caseStageID (PK)
    caseStage

    Now I need a query which would display only the latest of the case details. It is quiet possible that a case is posted for evidence on say 1/10/2010 and on that day it is decreed. So my caseStageTran would hold both data in diff rows. Little complexity is, if the caseStageID is within 35,55,65,70 then that whole case record should not be part of the query result!

    Sample data of caseStageTran...
    Code:
    caseID     caseStageID                  caseDate
    1                 15                   1/1/10
    2                 20                   2/10/10
    1                 17                   10/10/10
    1                 35                    10/10/10
    3                 12                    15/10/10
    4                 22                    16/10/10
    3                 55                    15/10/10
    4                 70                    18/10/10
    Any suggestion or changes in the table structure is welcome...

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Acc 2003 Problem with Query to return latest record

    Try it out with the following two Queries (Table Name is Tables5 as example):

    Query Name: ExcludeQ

    Code:
    SELECT Table5.CaseID, Table5.CaseStageID
    FROM Table5
    GROUP BY Table5.CaseID, Table5.CaseStageID
    HAVING (((Table5.CaseStageID) In (35,55,65,70)));
    Second Query: OutputQ

    Code:
    SELECT Table5.*
    FROM Table5
    WHERE (((Table5.CaseID) Not In (SELECT CaseID from ExcludeQ)));
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Dec 2003
    Posts
    24

    Exclamation

    That was a good one apr pillai
    This code extracted all the records which had the excluded list of caseStageIDs
    Code:
    SELECT CaseStagesTran.caseID, CaseStagesTran.caseStageID
    FROM CaseStagesTran
    GROUP BY CaseStagesTran.caseID, CaseStagesTran.caseStageID
    HAVING (((CaseStagesTran.caseStageID) In (31,35,55,65,68,70)));
    And this extracted the list of records not forming part of the above query list
    Code:
    SELECT CaseStagesTran.*
    FROM CaseStagesTran
    WHERE (((CaseStagesTran.CaseID) Not In (SELECT CaseID from ExcludeQ)));
    Now we need to extract only the latest date record of the of each caseID of outputQ

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Query to return latest records

    If you record the time along with the Casedate (using Now() function), instead of date alone as you do now, it will not be a problem to identify and filter the latest record.

    If this is done then you can create a Total Query grouped on CaseID and Max(CaseDate) columns which will give you a recordset that identifies the latest records. This Query you can use to link, with the Main Recordset in a Query, on CaseID to extract the latest transaction records.

    Or you may create a separate field to record Data entry/Editing activity Date/Time through the Before Update EventProcedure it will serve the same purpose. You may use one of the two methods in your projects and it is a must.

    You may go through the following blog post to know how important it is:

    http://msaccesstips.com/2008/01/who-changed-data/
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  5. #5
    Join Date
    Dec 2003
    Posts
    24

    Acc 2003

    I get the below message in the link you sent..
    NOT FOUND
    Sorry, but you are looking for something that isn't here.

  6. #6
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Acc 2003 Problem with Query to return latest record

    I am sorry about the Error, please find the correct link below:

    Who changed the Data
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •