I have a query which has 10 fields, 3 of which I want to Group by First. The object being, to automatically find the next roll of in date material, with the nearest expiry date and add it to a part manufacturing document.

There may be multiple different materials for each part and multiple rolls in a lot.

The fields are:
Expiry Date (Date format)
Roll Number (Number format)
Lot No (Text format)

When I run the Query with the above fields each Grouped by First both Expiry Date and Roll Number work as intended but Lot No doesn t filter at all. I m assuming that it s because Lot No is a Text field?

Typical Lot No format are as follows: JH5LCQ8096 or 081716-1T3 or 17-0543
In any event I can t get it to work.

I ve tried grouping the first two and then running a second query for Lot No but that fails too.

FYI Expired Date is also filtered to remove expired material and there is a True False statement to weed out consumed material. Which leaves me with data for In Date & Unconsumed material.

---

Reply from Alex Hedley:

So you want to order Lot No alphabetically?