Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Atlanta, GA
    Provided Answers: 2

    Question Unanswered: Selecting most recent date for each distict lot #?

    I have a real brain burner here. This is for a manufacturing facility. Each day, each Lot# has some location in the plant. This location is recorded in the Inventory_Archive table. with a date stamp called Activity_Date. The following day, a new record is created with the current inventory location and date stamp. And so on and so on. The each Lot# has a record for every day, showing its location day to day. With me so far?

    For each unique Lot#, I need to select the record with the most recent Activity_Date (which can be any date, just so long as it is the most recent for that particular Lot #). Any ideas?

  2. #2
    Join Date
    Aug 2009
    Up Nort' Wi
    Couple ideas/options off the top of my head.

    I'm making a couple assumptions here:
    1. LotNumber is a number field
    2. You're working with a recordset

    Either one of the SQL strings should produce the same result. After being burned a few times I tend to shy away from the Last() doesn't always work the way you'd expect it to, and can cause some convoluted nested queries.

    I aliased the field primarily so the recordset would work with either version of the SQL string.

    	strSQL = "SELECT Last(Activity_Date) As ActivityDate " _
    		&"FROM " _ 
    		&"(" _
    			&"SELECT Activity_Date " _
    			&"FROM Inventory_Activity " _
    			&"WHERE LotNumber = YourLotNumber " _
    			&"ORDER BY Activity_Date " _
    		&") AS Tbl1;"
    	strSQL = "SELECT Activity_Date As ActivityDate " _
    		&"FROM Inventory_Activity " _
    		&"WHERE LotNumber = " &YourLotNumber _
    		&"ORDER BY Activity_Date;"
    Private Function GetLastActivityDate (YourLotNumber as Long) as Date
    	Dim db as DAO.Database
    	Dim rs as DAO.Recordset
    	Dim strSQL as String
    	Set db = CurrentDb
    	Set rs = db.OpenRecordset(strSQL)
    	If Not rs.BOF And Not rs.EOF Then
    		rs.MoveLast ' Only need this if using the second SQL String
    		GetLastActivityDate = rs!ActivityDate
    	End If
    	Set rs = Nothing
    	Set db = Nothing
    End Function
    Sam, hth
    Good, fast, cheap...Pick 2.

Posting Permissions

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