Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unhappy Unanswered: Max Date Subquery Help Needed

    Hello!

    This is my first post, though I have used this site numerous times! I have already searched for the answer and a few items have been close to what I needed by not quite or I am not understanding them fully.

    I have a query I am using to pull the most recent record for each student for a user-defined time-frame. Setup is as follow:
    1: Displays StudentID
    2: Displays the Max of either Entry_Date or Status_Date (Expression field as follows:
    MyStatusStart: Max(IIf(Month([Entry_Date])>Month([Status_Start]),[Entry_Date],[Status_Start]))
    3: Has a Where clause used as a Filter as follows:
    IIf(Month([Entry_Date])>Month([Status_Start]),[Entry_Date],[Status_Start])


    I am using the above query in a seond query. There, I place it into an Inner Join relationship with the source table (which has too many records and fields to work with directly or the query gets too complex) to filter the results down.

    Now this almost works perfectly accept for the following instances. A Student may have multiple records as they change status in the system. Sometimes the status is changed on the same day - resulting in a duplicate date in the Status_Start field.

    If I have a student like this...

    ID Status Status_Date Entry_Date
    2345 ACT 11/20/2006 11/21/2006
    2345 TRM 11/20/2006 11/25/2006

    I want it to only display the 2nd record. I believe I need a subquery to perform this however when I try, it works for the above instance but completely filters out a record like this:

    ID Status Status_Date Entry_Date
    2345 ACT 11/05/2006 11/19/2006
    2345 TRM 11/18/2006 11/07/2006

    In this case, I need it to ignore the entry date and just return the max of the Status_Date.

    If it is any help, the current SQL for my filtering down query is as follows:
    SELECT tblStudentStatus.Student, Max(IIf(Month([Entry_Date])>Month([Status_Start]),[Entry_Date],[Status_Start])) AS MyStatusStart
    FROM tblStudentStatus
    WHERE (((IIf(Month([Entry_Date])>Month([Status_Start]),[Entry_Date],[Status_Start])) Between #11/1/2006# And #11/30/2006#))
    GROUP BY tblStudentStatus.Student;

    Any assistance would be greatly appreciated - If more information is needed or it looks like I am going about this the wrong way, please let me know. If it is very lengthy feel free to email me @ Xythnia@yahoo.com and I will post the solution on the thread afterwards.

    Thank You!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I solve this problem by adding a sequential record # (or count) as part of the table then you can get the lastmost by ordering by the date and record #
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Isn't this going to fail you in a few days, when the Month rolls back to 1?
    If you have a student with a status_start date 12/27/06 and an entry_date of 1/4/07, you're going to see the record for 12/27/06. I'd leave Month out of the comparison altogether.
    Inspiration Through Fermentation

Posting Permissions

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