Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Columbia, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Previous to Last Records (XP)

    Greetings:
    I need to retrieve the last and previous to last records of a date field. On catch is that the dates are not in any specific order.
    Does anyone know how to do this?
    Here is my query:
    SELECT Temp.svcr_id, Last(Temp.complnc_doc_recvd_dt) AS Last_Date
    FROM Temp
    GROUP BY Temp.svcr_id;
    Thanks,
    Luis

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

    Re: Previous to Last Records (XP)

    Welcome to Woody's Lounge!

    Create a query based on Temp.
    Add the svcr_id and complnc_doc_recvd_dt fields (and others if you need them).
    Set the sort order for complnc_doc_recvd_dt to Descending.
    Activate the Properties window.
    Click in an empty part of the upper part of the query window, so that you see the query properties.
    Enter 2 in the Top Values property (you cannot select 2 from the dropdown list, but you can type it in).

    That's it!

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    Columbia, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Previous to Last Records (XP)

    I have about 311 records that have last and previous dates. Is there anyway to do them all at the same time? Eventually I want to automate this so it can be done at once even if it takes several steps to get there.

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

    Re: Previous to Last Records (XP)

    Let's start again.

    Step 1: create a query to retrieve the last and next to last dates.

    Create a new query in design view, add Temp.
    Select View | Totals to make a totals query.
    Add only the complnc_doc_recvd_dt field.
    Set the sort order for complnc_doc_recvd_dt to Descending.
    Activate the Properties window.
    Click in an empty part of the upper part of the query window, so that you see the query properties.
    Enter 2 in the Top Values property (you cannot select 2 from the dropdown list, but you can type it in).
    Save this query as qryTop2.

    Step 2: create a query to retrieve all records for the last and next to last dates.

    Create a new query in design view, add Temp and qryTop2.
    Drag a line from complnc_doc_recvd_dt in Temp to the same field in qryTop2 to join them.
    Add the fields from Temp you want to return.

Posting Permissions

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