Find records with sooner Expiry Date (FEFO regulation)

[josnghi]

Please see the table inventory below

Material	BUn	Good Rec Date	Batch	Unrestricted	Expiry Date
300008	KG	09/04/2015	0000033574	32,000	18/01/2017
300008	KG	14/05/2015	0000033927	24,725	15/02/2017
300008	KG	03/06/2015	0000034121	24,725	02/11/2016
300058	KG	04/06/2015	0000034155	6,300	05/02/2017
300058	KG	06/06/2015	0000034168	36,000	08/02/2017
300058	KG	09/06/2015	0000034214	24,975	07/04/2017
300058	KG	09/06/2015	0000034218	10,800	08/04/2017

I want to find the records at the same material which have the greater Batch but sooner Expiry Date (the records highlighted above) so that we can issue by FEFO (First Expiry First Out) regulation. The table have large records so we can not find out manually
I try to use the DLookup in the Query but the results is not correct
Could you please help me?
Thanks in advance

[amrut]

why use Dlookup in query ? Simply sort in ascending order of the Expiry date in your query.

[orange]

Perhaps there is more to the story than you have provided in your post.
amrut seems to have the answer for identifying ExpiryDates in Date sequence.

[josnghi]

Thanks you for Amrut and Orange's advice. Currently, Our system (SAP) sort out by Batch as default so that we can issue by FIFO (First in First Out). But sometime we have to find records with sooner Expiry Date to follow FEFO manually. I export data from SAP to excel then inport data to access as table above to find out records with sooner Expiry Date. I used to sort in ascending order of Expiry Date in access to find records with sooner Expiry Date. But it is difficult for me to find records sorted ascending order by Batch in the database with large records. Do you have any idea help me. Thanks you very much

[June7]

Still not clear to me what you want. The example shows only one record desired for material 300008. Nothing for material 300058. If you want the most recent record for each group (material?), review: http://allenbrowne.com/subquery-01.html#TopN

[josnghi]

Thanks you for June7's advising me to review subquery. The example shows only one Batch 0000034121 of material 300008 needed to find, the material 300058 is being followed the FIFO and FEFO. So no need to find material 300058. The database have large materials. Each material have large Batches. I try to find Batches with sooner Expiry Date for the whole database. Thanks so much