Help to simplify complex query

[NeilT123]

With the help of this forum I have built quite a complicated query.

The query is in 4 parts 2 SELECT, 1 UNION, 1 CROSSTAB and each section of code is shown below.

The query returns the correct results but I wondered whether by building it piece by piece that I had made it too complex.

If anyone has a view on whether this can be simplified it would be appreciated.

I know it is probably not the norm to post a question like this but I have learnt so much from this forum and the complexity of this query concerns me.

Thank you in advance for any input.

QRY1FMA1

SELECT Format([OMApplicationMonth],"yyyy") AS [OM Application Year], tblOMApplications.OMApplicationIndex, tblFieldDetails.FieldCode, tblFieldDetails.FieldName, tblOMApplications.OMApplicationRate, tblOMApplications.OMApplicationMonth, tblOMApplications.OMApplicationType, IIf([OMApplicationType]="rapidly incorporated into soil","Soil incorporation is assumed as within 6 hrs for slurries and within 24 hrs for manures","") AS [OM application Comment], Format([OMApplicationMonth],"mmm") AS [Month], tblOMValues.ManureType, tbLUSoilType.OMSoilTextureCoding, IIf([month] In ("feb","mar","apr"),1,IIf([month] In ("may","jun","jul"),2,IIf([month] In ("aug","sep","oct"),3,IIf([month] In ("nov","dec","jan"),4)))) AS [season Code], [NValue]*[OMApplicationRate] AS [Total N applied], [P2O5Value]*[OMApplicationRate] AS [Total P2O5 applied], [K2OValue]*[OMApplicationRate] AS [Total K2O applied], [MgOValue]*[OMApplicationRate] AS [Total MgO applied], [SO3Value]*[OMApplicationRate] AS [Total SO3 applied]
FROM (tblOMValues INNER JOIN ((tblSoilType INNER JOIN (tblFieldDetails INNER JOIN tblOMApplications ON tblFieldDetails.FieldCode = tblOMApplications.FieldCode) ON tblSoilType.SoilTypeCode = tblFieldDetails.SoilTypeCode) LEFT JOIN tbLUSoilType ON tblSoilType.SoilType = tbLUSoilType.SoilType) ON tblOMValues.ManureTypeIndex = tblOMApplications.ManureTypeIndex) INNER JOIN tblSelect ON tblFieldDetails.FarmAccountNumber = tblSelect.FarmAccountNumber;

QRY2FMA1

SELECT qry1FMA1.[OM Application Year], qry1FMA1.FieldName, qry1FMA1.FieldCode, qry1FMA1.OMApplicationIndex, qry1FMA1.OMApplicationMonth, qry1FMA1.Month, qry1FMA1.ManureType, qry1FMA1.OMApplicationType, qry1FMA1.[season Code], qry1FMA1.[Total N applied], qry1FMA1.[Total P2O5 applied], qry1FMA1.[Total K2O applied], qry1FMA1.[Total MgO applied], qry1FMA1.[Total SO3 applied], tblOManureNitrogenCalculation.Factor, tblOManureNitrogenCalculation.FactorYr2, tblOManureNitrogenCalculation.FactorYr3, IIf([Month]="Jan" Or [season code]=1 Or [season code]=2,([Total N applied]*[factor]/100),0) AS NYr0, IIf([month]="Jan" Or [season code]=1 Or [season code]=2,([Total N applied]*[FactorYr2]/100),([Total N applied]*[factor]/100)) AS NYr1, IIf([month]="Jan" Or [season code]=1 Or [season code]=2,([Total N applied]*[FactorYr3]/100),([Total N applied]*[FactorYr2]/100)) AS NYr2
FROM qry1FMA1 INNER JOIN tblOManureNitrogenCalculation ON (qry1FMA1.[season Code] = tblOManureNitrogenCalculation.SeasonCode) AND (qry1FMA1.OMSoilTextureCoding = tblOManureNitrogenCalculation.OMSoilTextureCoding) AND (qry1FMA1.ManureType = tblOManureNitrogenCalculation.ManureType) AND (qry1FMA1.OMApplicationType = tblOManureNitrogenCalculation.OMApplicationType);

QRY3FMA1

SELECT [OMApplicationIndex],[FieldName],[FieldCode],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied], NYr0 as Effect, [OM Application Year] As Yr
FROM qry2FMA1
UNION ALL
SELECT [OMApplicationIndex],[FieldName],[FieldCode],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied], NYr1, [OM Application Year]+1
FROM qry2FMA1
UNION ALL SELECT [OMApplicationIndex],[FieldName],[FieldCode],[Total N applied],[Total P2O5 applied],[Total K2O applied],[Total MgO applied],[Total SO3 applied],Nyr2, [OM Application Year]+2
FROM qry2FMA1;

QRY4FMA1

TRANSFORM Sum(qry3FMA1.Effect) AS SumOfEffect
SELECT qry3FMA1.OMApplicationIndex, qry3FMA1.FieldName, qry3FMA1.FieldCode, tblOMApplications.OMApplicationMonth, tblOMValues.ManureConsistency, IIf([ManureConsistency]="Solid","t/ha","m3/ha") AS ApplicationRateUnits, tblOMValues.ManureType, tblOMApplications.OMApplicationType, tblOMApplications.OMApplicationRate, qry3FMA1.[Total N applied], qry3FMA1.[Total P2O5 applied], qry3FMA1.[Total MgO applied], qry3FMA1.[Total K2O applied], qry3FMA1.[Total SO3 applied]
FROM (tblOMValues INNER JOIN tblOMApplications ON tblOMValues.ManureTypeIndex = tblOMApplications.ManureTypeIndex) INNER JOIN qry3FMA1 ON tblOMApplications.OMApplicationIndex = qry3FMA1.OMApplicationIndex
GROUP BY qry3FMA1.OMApplicationIndex, qry3FMA1.FieldName, qry3FMA1.FieldCode, tblOMApplications.OMApplicationMonth, tblOMValues.ManureConsistency, IIf([ManureConsistency]="Solid","t/ha","m3/ha"), tblOMValues.ManureType, tblOMApplications.OMApplicationType, tblOMApplications.OMApplicationRate, qry3FMA1.[Total N applied], qry3FMA1.[Total P2O5 applied], qry3FMA1.[Total MgO applied], qry3FMA1.[Total K2O applied], qry3FMA1.[Total SO3 applied]
ORDER BY tblOMApplications.OMApplicationMonth DESC 
PIVOT qry3FMA1.Yr In (2002,2003,2004,2005,2006,2007,2008);