Duplicates, removing by date?

[plog]

It's going to take 2 sub-queries now. Here's the SQL:

Name the below query 'FirstDue_sub_1'

SELECT LiveData.Cls1, Min(LiveData.[Mfg End Date]) AS FirstDue
FROM LiveData
GROUP BY LiveData.Cls1;

Name the below query 'FirstDue_sub_2'

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:

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;

 

[MikeDuffield]

Thanks Plog, when I run the final query I get:

"The specified field 'LiveData.Batch' could refer to more than one table in the FROM clause of your SQL statement"

I've had a look at it and can't figure out what the problem is - looks to me like LiveData is specified wherever it needs to be... Am I missing something really obvious?

 

[plog]

Could you post your SQL? Did you change it from what I had?

Also, run sub_1 by itself and see if you get any errors. Then run sub_2 to see if you get any errors there.

 

[MikeDuffield]

No I didn't change anything, I also made sure I saved them with the right name. I tried twice just in case I'd missed something... It's identical to what you posted.

 

[plog]

Were you able to run the sub queries individually?

 

[MikeDuffield]

Yeah both sub queries ran fine, just the last one gave me that error.