Return latest entry

[nancy54]

Hello,
I have a table for entering the status of a production batch with a DateStamp (Now() criteria) field, and a LotNumber field (which will contain multiple entries of the same lot). I want to run a query the returns only the latest entry for each lot. Any Ideas. I'm not good with VBA. I was trying to use the DMax function but getting errors.

Thanks, Nancy

 

[plog]

No VBA, or even DMax needed, just SQL. First get the latest Datestamp for each LotNumber:

SELECT LotNumber, MAX(DateStamp) AS LastEntryDateStamp
FROM YourTableNameHere
GROUP BY LotNumber

Save that as 'sub1'. Then build a new query using it and YourTableNameHere. Join them by LotNumber and then DateStamp to LastEntryDateStamp and bring in every field from YourTableNameHere you want in the query.

 

[nancy54]

Thank you Plog,
Sorry for not getting back sooner. Below is my SQL as it is now. I have a criteria in one of the fields. Where would I place the code you suggested in this SQL statement??

SELECT tblStartNewBatch.[Item Part Number], tblStartNewBatch.[Lot Number], qryDescByLot.Description, tblUpdateBatch.[Production Status], tblUpdateBatch.Adj
FROM (tblStartNewBatch INNER JOIN tblUpdateBatch ON tblStartNewBatch.[Lot Number] = tblUpdateBatch.[Lot Number]) INNER JOIN qryDescByLot ON tblStartNewBatch.[Lot Number] = qryDescByLot.[Lot Number]
WHERE ((Not (tblUpdateBatch.[Production Status])="Approved Batch"));

 

[plog]

If that's the basis of your data, then use it as 'YourTableNameHere' in my instructions.

 

[nancy54]

Plog, I decided to do what you suggested originally and extract the Lot Numbers I need first. Here is my SQL code, but I'm getting the following error when I run:

SELECT Lot Number, MAX(DateStamp) AS LastEntryDateStamp
FROM tblUpdateBatch;
GROUP BY Lot Number

Syntax error (missing operator) in query expression 'Lot Number'.

 

[plog]

When you use spaces and special characters in your field names these are the problems you encounter. My suggestion is you remove the spaces in your field names (and tables if you have any). However, you can get around that by putting brackets around your field names:

[Lot Number]

 

[nancy54]

First I tried the brackets- didn't work, so I fixed the field name- no spaces, but now I am getting the following:

The LEVEL clause includes a reserve word of argument that is misspelled or missing, or the punctuation is incorrect.

SELECT LotNumber, MAX(DateStamp) AS LastEntryDateStamp
FROM tblUpdateBatch;
GROUP BY LotNumber

 

[nancy54]

Plog, I did some research- turns out I had to remove the semicolon in the FROM clause.
It works perfect. I think this will help me out. Thanks much!!

Nancy

 

[isladogs]

2 things:
1. The ; should only be used at the end of the SQL statement
2. In an earlier reply LetNumber was in the table tblStartNewBatch.
So perhaps you need:

SELECT LotNumber, MAX(DateStamp) AS LastEntryDateStamp
FROM tblStartNewBatch
GROUP BY LotNumber