Return latest entry (1 Viewer)

nancy54

Registered User.
Local time
Today, 06:01
Joined
Jun 19, 2018
Messages
49
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

Banishment Pending
Local time
Today, 05:01
Joined
May 11, 2011
Messages
11,611
No VBA, or even DMax needed, just SQL. First get the latest Datestamp for each LotNumber:

Code:
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

Registered User.
Local time
Today, 06:01
Joined
Jun 19, 2018
Messages
49
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

Banishment Pending
Local time
Today, 05:01
Joined
May 11, 2011
Messages
11,611
If that's the basis of your data, then use it as 'YourTableNameHere' in my instructions.
 

nancy54

Registered User.
Local time
Today, 06:01
Joined
Jun 19, 2018
Messages
49
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

Banishment Pending
Local time
Today, 05:01
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 06:01
Joined
Jun 19, 2018
Messages
49
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

Registered User.
Local time
Today, 06:01
Joined
Jun 19, 2018
Messages
49
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

MVP / VIP
Local time
Today, 10:01
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom