Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (Access 2000)

    I have a table that contains mutiple records for an individual. For any given individual there could be one or more records. The current record will not have a value in the ACTUAL_TERMINATION_DATE field. All previous records for an individual will have a date in that field. What I am looking for is a query that will exclude the null date value and return the next most recent record.

    I created a query I thought would work, but it just churns away and does not retun the data. Here is the SQL for the query. It uses a subselect to find the Max(Period_of_Service_ID) for each Person_ID.

    SELECT tblPeriodOfService_Reasons.PERSON_ID, tblPeriodOfService_Reasons.LEAVING_REASON, tblPeriodOfService_Reasons.PERIOD_OF_SERVICE_ID, tblPeriodOfService_Reasons.ACTUAL_TERMINATION_DATE
    FROM tblPeriodOfService_Reasons
    WHERE (((tblPeriodOfService_Reasons.PERIOD_OF_SERVICE_ID ) = (SELECT MAX( Period_of_Service_ID) FROM tblPeriodOfService_Reasons A WHERE A.Person_ID = tblPeriodOfService_Reasons.Person_ID)));


    Is there a different / better way to go about getting the record in question?

    Thanks for any help
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query (Access 2000)

    You state that you want to return the record with the most recent ACTUAL_TERMINATION_DATE for each individual. Shouldn't you have a criteria on that field, with a subquery

    SELECT MAX(ACTUAL_TERMINATION_DATE) FROM tblPeriodOfService_Reasons A WHERE A.Person_ID = tblPeriodOfService_Reasons.Person_ID

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 2000)

    That should work as well as with the Period_Of_Service_ID. The MAX(Date) would also have the MAX(POS_ID).

    I have replaced the subselect with the MAX(Date) version, but am getting the same results.

    The query just sits and churns, but does not return any data.
    Richard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query (Access 2000)

    OK, you need two queries:

    1) A totals query that returns the most recent date for each individual:

    SELECT PERSON_ID, Max(ACTUAL_TERMINATION_DATE) AS MaxDate
    FROM tblPeriodOfService_Reasons
    GROUP BY PERSON_ID

    Don't include other fields in this query! Save it as (for example) qryMaxDate.

    2) A query based on the table and on the query you just saved, joined on PERSON_ID and on ACTUAL_TERMINATION_DATE vs MaxDate. Add * from the table to the query grid:

    SELECT tblPeriodOfService_Reasons.*
    FROM qryMaxDate INNER JOIN tblPeriodOfService_Reasons ON (qryMaxDate.MaxDate = tblPeriodOfService_Reasons.ACTUAL_TERMINATION_DATE ) AND (qryMaxDate.PERSON_ID = tblPeriodOfService_Reasons.PERSON_ID);

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Access 2000)

    That works MUCH better


    Thanks
    Richard

Posting Permissions

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