return Max 1 then max 2 1

[NeilT123]

I have a query as follows:

 SELECT tblFieldDetails.FarmAccountNumber, tblCropping.FieldCode, Max(tblCropping.CroppingYear) AS [MaxOfCropping Year], Max(tblCropping.CropOrder) AS [MaxOfCrop Order]
FROM (tblFieldDetails LEFT JOIN tblCropping ON tblFieldDetails.FieldCode = tblCropping.FieldCode) INNER JOIN tblSelect ON tblFieldDetails.FarmAccountNumber = tblSelect.FarmAccountNumber
GROUP BY tblFieldDetails.FarmAccountNumber, tblCropping.FieldCode;

The results give two independent MaxOf answers so the MaxOf the cropping year and the Max of the Crop order for each field code.

What I really want is a query that calculates the MaxOf cropping year and then calculates the Max of Crop order but only for the already calculated MaxOfCropping Year.

So for example if Field A had
cropping year 2004 and for that year had crop order 8 & 9
cropping year 2005 and for that year crop order 5 & 6
Cropping year 2006 and for that year crop order 1 & 2
my query returns MaxOfCroppingYear 2006 MaxOfCropOrder 9

but what I want it to return is MaxOfCroppingYear 2006 MaxOfCropOrder 2

Hope this makes sense, brain is shredded. Thanks in advance for any help

 

[rac2]

Obtain the MaxOfCropOrder for each year.

SELECT tblFieldDetails.FarmAccountNumber, 
        tblCropping.FieldCode, 
        tblCropping.CroppingYear, 
        Max(tblCropping.CropOrder) AS [MaxOfCropOrder] 
FROM tblFieldDetails 
LEFT JOIN tblCropping ON tblFieldDetails.FieldCode =  
          tblCropping.FieldCode 
INNER JOIN tblSelect ON tblFieldDetails.FarmAccountNumber = 
           tblSelect.FarmAccountNumber
GROUP BY tblFieldDetails.FarmAccountNumber, 
         tblCropping.FieldCode;

Save that query with the name LastCropOfYear.

Then obtain the most recent year.

SELECT tblFieldDetails.FarmAccountNumber, 
        tblCropping.FieldCode, 
        Max(tblCropping.CroppingYear)AS [MaxOfCropping Year] 
FROM tblFieldDetails 
LEFT JOIN tblCropping ON tblFieldDetails.FieldCode =  
          tblCropping.FieldCode 
INNER JOIN tblSelect ON tblFieldDetails.FarmAccountNumber = 
           tblSelect.FarmAccountNumber
GROUP BY tblFieldDetails.FarmAccountNumber, 
         tblCropping.FieldCode;

Save that one with the name LastYearWithCrops

Then JOIN these two queries to the basic tables.

SELECT tblFieldDetails.FarmAccountNumber, 
        tblCropping.FieldCode, 
        LastYearWithCrops.MaxOfCropping Year,
        LastCropOfYear.MaxOfCropOrder
FROM tblFieldDetails 
LEFT JOIN tblCropping ON tblFieldDetails.FieldCode =  
          tblCropping.FieldCode 
INNER JOIN tblSelect ON tblFieldDetails.FarmAccountNumber = 
           tblSelect.FarmAccountNumber
LEFT JOIN LastCropOfYear ON 
                LastCropOfYear.FarmAccountNumber = 
                     tblSelect.FarmAccountNumber
            AND LastCropOfYear.FieldCode =  
                   tblCropping.FieldCode
LEFT JOIN LastYearWithCrops ON 
                LastYearWithCrops.FarmAccountNumber = 
                     tblSelect.FarmAccountNumber
            AND LastYearWithCrops.FieldCode =  
                   tblCropping.FieldCode

This may need to be tweaked some to fit your table structure, I dont quite see what the relationships among them mean. The main point is that you need separate GROUP BY queries to get the latest years and crop orders for the years. These queries are then joined to the basic tables for the complete result.

 

[NeilT123]

Hi Rac2
I copied and pasted your code but it threw a syntax error. Unfortunatly I am away from now until Wednesday but I can see what you are suggesting and will try again when I am back. Thank you for your help