It's going to take 2 sub-queries now. Here's the SQL:
Name the below query 'FirstDue_sub_1'
Name the below query 'FirstDue_sub_2'
The below query is the one that will produce your results. This one you can go into and add any additional fields from 'LiveData' that you need:
Name the below query 'FirstDue_sub_1'
Code:
SELECT LiveData.Cls1, Min(LiveData.[Mfg End Date]) AS FirstDue
FROM LiveData
GROUP BY LiveData.Cls1;Name the below query 'FirstDue_sub_2'
Code:
SELECT FirstDue_sub_1.Cls1, FirstDue_sub_1.FirstDue, Max(LiveData.Qty) AS MaxQty
FROM FirstDue_sub_1 INNER JOIN LiveData ON (FirstDue_sub_1.FirstDue = LiveData.[Mfg End Date]) AND (FirstDue_sub_1.Cls1 = LiveData.Cls1)
GROUP BY FirstDue_sub_1.Cls1, FirstDue_sub_1.FirstDue;The below query is the one that will produce your results. This one you can go into and add any additional fields from 'LiveData' that you need:
Code:
SELECT LiveData.Cls1, LiveData.[Mfg End Date], LiveData.Qty, LiveData.Batch
FROM FirstDue_sub_2 INNER JOIN LiveData ON (FirstDue_sub_2.MaxQty = LiveData.Qty) AND (FirstDue_sub_2.FirstDue = LiveData.[Mfg End Date]) AND (FirstDue_sub_2.Cls1 = LiveData.Cls1)
GROUP BY LiveData.Cls1, LiveData.[Mfg End Date], LiveData.Qty, LiveData.Batch;
