Query Optimization

[georgesOne]

I have a query like follows - collecting data for certain years, actually for ten years ahead -

SELECT qryProjects.ProjectID, qryProjects.Project, qryProjects.Layers, qryProjects.DispenseVolume, Round(DLookUp("WS2010","tblPlateStarts","Project = '" & [Project] & "'")) AS Volume2010, Format(qryProjects.InitialPrice,"Currency") AS Price2010, Format(qryProjects.EstimatedCOGS,"Currency") AS COGS2010, Format([Price2010]*[Volume2010],"Currency") AS Sales2010, Format(1-[COGS2010]/[Price2010],"Percent") AS GM2010, 
Round(DLookUp("WS2011","tblPlateStarts","Project = '" & [Project] & "'")) AS Volume2011, DLookUp("PriceCHange","tblDefaultChanges","YearChange = " & 2011) AS PD2011, DLookUp("COGChange","tblDefaultChanges","YearChange = " & 2011) AS CC2011, Format([Price2010]*(1+[PD2011]),"Currency") AS Price2011, Format([COGS2010]*(1+[CC2011]),"Currency") AS COGS2011, Format([Price2011]*[Volume2011],"Currency") AS Sales2011, Format(1-[COGS2011]/[Price2011],"Percent") AS GM2011, 
Round(DLookUp("WS2012","tblPlateStarts","Project = '" & [Project] & "'")) AS Volume2012, DLookUp("PriceChange","tblDefaultChanges","YearChange = " & 2012) AS PD2012, DLookUp("COGChange","tblDefaultChanges","YearChange = " & 2012) AS CC2012, Format([Price2011]*(1+[PD2012]),"Currency") AS Price2012, Format([COGS2011]*(1+[CC2012]),"Currency") AS COGS2012, Format([Price2012]*[Volume2012],"Currency") AS Sales2012, Format(1-[COGS2012]/[Price2012],"Percent") AS GM2012, 
......
FROM qryProjects;

and I wonder, if there is not an easier way to generate such a query. This is currently all hard-coded, but I guess it must - actually should - not be, as next year another year will become active, and the current first year data will be deleted.

Any suggestions?
Thanks, georgesOne

 

[georgesOne]

Sorry, obviously I always find the solution only after I have posted the question here.
The solution is

DoCmd.Close acQuery, "qryTest"
DoCmd.DeleteObject acQuery, "qryTest"
strSQL = "SELECT qryProjects.ProjectID, qryProjects.Project, qryProjects.Layers, qryProjects.DispenseVolume, "
For i = 2010 To 2020
S1 = "WS" & i
S2 = " & qryProjects.Project & "
strSQL = strSQL & "Round(DLookUp(" & DQ & S1 & DQ & ", " & DQ & "tblPlateStarts" & DQ & ", " & DQ & "Project ='" & DQ & S2 & DQ & "'" & DQ & ")) AS Volume" & i & ", "
strSQL = strSQL & "DLookUp('PriceChange', 'tblDefaultChanges','YearChange = " & i & "') As PD" & i & ", "
strSQL = strSQL & "DLookUp('COGChange','tblDefaultChanges','YearChange = " & i & "') As CC" & i & ", "
If i = 2010 Then
strSQL = strSQL & "Format(qryProjects.InitialPrice,'Currency') AS Price" & i & ", "
strSQL = strSQL & "Format(qryProjects.EstimatedCOGS,'Currency') AS COGS" & i & ", "
strSQL = strSQL & "Format([Price" & i & "]*[Volume" & i & "],'Currency') AS Sales" & i & ", "
strSQL = strSQL & "Format(1-[COGS" & i & "]/[Price" & i & "],'Percent') AS GM" & i & ", "
Else
strSQL = strSQL & "Format([Price" & i - 1 & "]*(1+[PD" & i & "]),'Currency') AS Price" & i & ", "
strSQL = strSQL & "Format([COGS" & i - 1 & "]*(1+[CC" & i & "]),'Currency') AS COGS" & i & ", "
strSQL = strSQL & "Format([Price" & i & "]*[Volume" & i & "],'Currency') AS Sales" & i & ", "
strSQL = strSQL & "Format(1-[COGS" & i & "]/[Price" & i & "],'Percent') AS GM" & i & ", "
End If
MsgBox strSQL
Next
strSQL = Left(strSQL, Len(strSQL) - 2) & " from qryProjects;"

Set db = CurrentDb()
Set MyQueryDef = db.CreateQueryDef("qryTest", strSQL)
DoCmd.OpenQuery "qryTest"

Anyway, thanks for 'helping' and for having this forum. The dates will be variables of course.
Kind regards, georgesOne

 

[dhookom]

Actually, I think there might be a more efficient method but your table structure isn't clear. It looks like tblDefaultChanges is un-normalized.

From your SQL, I assume you basically need a multiple value crosstab query.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Duane,
thanks for the tip.Yes I agree - some further look needs to be done into the tables.
I am trying to convert an evergrowing excel sheet into access.
The sheet is pretty messed up and out of control, but as usual I need to show some intermediate progress even though I do this during my free time.
Also I am thinking that some looks of excel should remain, because otherwise the users will have adaption issues.
Anyway, I guess I will ask more questions here within soon.
Thanks again, georgesOne

 

[dhookom]

georgesOne,
Thanks for the clarification and good luck.

Duane
Hook'D on Access
MS Access MVP