Need to improve nested query

[NeilT123]

I apologise for the length of this post but thought it best to show the whole process.

I have a complicated form, part of which is based on a temporary table. The table is created when the form is opened but the performance of the queries creating the temporary table is sluggish and I would appreciate any suggestions in how the query can be improved.

The codes are:

On opening the form:

Private Sub CmdFertApplns_Click()
DoCmd.SetWarnings False 'switch off warning messages re adding & deleting records
Dim strSQL As String
strSQL = "SELECT OMApplicationIndex, IncludeInReports, FieldCode, " & _
   "OMApplicationMonth, ManureConsistency, ApplicationRateUnits, " & _
   "ManureName, OMApplicationType, OMApplicationRate, " & _
   "TotalNapplied, TotalP2O5applied, TotalK2Oapplied, " & _
   "TotalMgOapplied, TotalSO3applied, [2002], [2003], [2004], " & _
   "[2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012] " & _
   "INTO tblTEMPfrmIFFertOM " & _
   "FROM qryfrmIFFertOMP4;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True 'reset warnings
DoCmd.OpenForm "frmIfFertAppln"
End Sub

The queries:
[qryfrmIFFertOMP1]

SELECT tblFieldDetails.FieldCode, tblCropping.CroppingYear, tblOMApplications.OMApplicationIndex, tblOMApplications.OMApplicationRate, tblOMApplications.OMApplicationMonth, tblOMValues.ManureTypeIndex, tblSoilType.OMSoilTextureCoding, Month([OMApplicationMonth]) AS MonthIndex, tblOMApplications.OMApplnTypeIndex, [NValue]*[OMApplicationRate] AS TotalNapplied
FROM ((tblSLTFarm INNER JOIN tblFieldDetails ON tblSLTFarm.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) INNER JOIN tblSoilType ON tblFieldDetails.SoilDescriptionCode = tblSoilType.SoilDescriptionCode) INNER JOIN (tblCropping INNER JOIN (tblOMValues INNER JOIN tblOMApplications ON tblOMValues.ManureNameIndex = tblOMApplications.ManureNameIndex) ON tblCropping.CroppingNumber = tblOMApplications.CroppingNumber) ON tblFieldDetails.FieldCode = tblCropping.FieldCode;

[qryfrmIFFertOMP2]

SELECT qryfrmIFFertOMP1.CroppingYear, qryfrmIFFertOMP1.FieldCode, qryfrmIFFertOMP1.OMApplicationIndex, qryfrmIFFertOMP1.OMApplicationMonth, qryfrmIFFertOMP1.MonthIndex, qryfrmIFFertOMP1.OMApplnTypeIndex, qryfrmIFFertOMP1.TotalNapplied, tblOMNitrCalculation.Factor, tblOMNitrCalculation.FactorYr2, tblOMNitrCalculation.FactorYr3, ([TotalNapplied]*[factor]/100) AS NYr0, ([TotalNapplied]*[FactorYr2]/100) AS NYr1, ([TotalNapplied]*[FactorYr3]/100) AS NYr2
FROM qryfrmIFFertOMP1 INNER JOIN tblOMNitrCalculation ON (qryfrmIFFertOMP1.OMApplnTypeIndex = tblOMNitrCalculation.OMApplnTypeIndex) AND (qryfrmIFFertOMP1.MonthIndex = tblOMNitrCalculation.MonthIndex) AND (qryfrmIFFertOMP1.OMSoilTextureCoding = tblOMNitrCalculation.OMSoilTextureCoding) AND (qryfrmIFFertOMP1.ManureTypeIndex = tblOMNitrCalculation.ManureTypeIndex);

[qryfrmIFFertOMP3]

SELECT [OMApplicationIndex],[FieldCode],[TotalNapplied], NYr0 as Effect, [CroppingYear] As Yr
FROM qryfrmIFFertOMP2
UNION ALL
SELECT [OMApplicationIndex],[FieldCode],[TotalNapplied], NYr1, [CroppingYear]+1
FROM qryfrmIFFertOMP2
UNION ALL SELECT [OMApplicationIndex],[FieldCode],[TotalNapplied],Nyr2, [CroppingYear]+2
FROM qryfrmIFFertOMP2;

[qryfrmIFFertOMP4]

TRANSFORM Sum(qryfrmIFFertOMP3.Effect) AS SumOfEffect
SELECT qryfrmIFFertOMP3.OMApplicationIndex, tblOMApplications.IncludeInReports, qryfrmIFFertOMP3.FieldCode, tblOMApplications.OMApplicationMonth, tblOMValues.ManureConsistency, IIf([ManureConsistency]="Solid","t/ha","m3/ha") AS ApplicationRateUnits, tblOMValues.ManureName, tbLUOMApplicationType.OMApplicationType, tblOMApplications.OMApplicationRate, qryfrmIFFertOMP3.TotalNapplied, [P2O5Value]*[OMApplicationRate] AS TotalP2O5applied, [K2OValue]*[OMApplicationRate] AS TotalK2Oapplied, [MgOValue]*[OMApplicationRate] AS TotalMgOapplied, [SO3Value]*[OMApplicationRate] AS TotalSO3applied
FROM (tblOMValues INNER JOIN (tblOMApplications INNER JOIN tbLUOMApplicationType ON tblOMApplications.OMApplnTypeIndex = tbLUOMApplicationType.OMApplnTypeIndex) ON tblOMValues.ManureNameIndex = tblOMApplications.ManureNameIndex) INNER JOIN qryfrmIFFertOMP3 ON tblOMApplications.OMApplicationIndex = qryfrmIFFertOMP3.OMApplicationIndex
GROUP BY qryfrmIFFertOMP3.OMApplicationIndex, tblOMApplications.IncludeInReports, qryfrmIFFertOMP3.FieldCode, tblOMApplications.OMApplicationMonth, tblOMValues.ManureConsistency, IIf([ManureConsistency]="Solid","t/ha","m3/ha"), tblOMValues.ManureName, tbLUOMApplicationType.OMApplicationType, tblOMApplications.OMApplicationRate, qryfrmIFFertOMP3.TotalNapplied, [P2O5Value]*[OMApplicationRate], [K2OValue]*[OMApplicationRate], [MgOValue]*[OMApplicationRate], [SO3Value]*[OMApplicationRate]
ORDER BY tblOMApplications.OMApplicationMonth DESC 
PIVOT qryfrmIFFertOMP3.Yr In (2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012);

As I said apologies for the length of this post.

My 1st thought is to create a new entry in the table [tblOMNitrCalculation] which would be an amalgamation of [ManureTypeIndex] [OMSoilTextureCoding] [MonthIndex] and [OMApplTypeIndex]. I would then concatenate those values is query [qryfrmIFFertOMP1] so that there is then only one match in query [qryfrmIFFertOMP2].

Is that a sensible idea? Are there any other suggestions as to how my set of queries can be improved?

I am self taught in Access and so apologise in advance if some of the coding is really basic but as always any suggestions are gratefully received.

Thank you in advance for any help given.

Neil

 

[ICCIIT]

Have you tried to add each query one at a time to identify which one or more is causing the slow down? my guess would be the Union query (depending on the number of records in both datasets)

I'm no expert on Union queries but should have both Union All Select and Union All in the same construct?

The last query in your post appears to contain multiple selection in to a Crosstab?

Feel free to ignore my post as I am first to admit that I maybe right off the mark.

 

[alvechurchdata]

Two other thoughts come to mind. One is that the fields used in the joins are all named "xxxxIndex" but are they all indexed? The other is the number of years covered. Are you carrying data for earlier years along until the final stage and could you filter it out earlier?


Geoff Franklin

http://www.alvechurchdata.co.uk