Here is the SQL but I'm not sure it would help as it is the final query in a line of subsequent queries that calculate, group, sum, etc
SELECT [Cost Authorisation Spend Report CAPITAL].[3 Digit Category], [Cost Authorisation Spend Report CAPITAL].Budget, [Cost Authorisation Spend Report CAPITAL].[Jan Actuals], [Cost Authorisation Spend Report CAPITAL].[Feb Actuals], [Cost Authorisation Spend Report CAPITAL].[Mar Actuals], [Cost Authorisation Spend Report CAPITAL].[Apr Actuals], [Cost Authorisation Spend Report CAPITAL].[May Actuals], [Cost Authorisation Spend Report CAPITAL].[Ju Actuals], [Cost Authorisation Spend Report CAPITAL].[Jul Actuals], [Cost Authorisation Spend Report CAPITAL].[Aug Actuals], [Cost Authorisation Spend Report CAPITAL].[Sep Actuals], [Cost Authorisation Spend Report CAPITAL].[Oct Actuals], [Cost Authorisation Spend Report CAPITAL].[Nov Actuals], [Cost Authorisation Spend Report CAPITAL].[Dec Actuals], [Cost Authorisation Spend Report CAPITAL].[Post Budget Year Actuals], [Cost Authorisation Spend Report CAPITAL].[Commitment to Spend], [Cost Authorisation Spend Report CAPITAL].[Total Spend]
FROM [Cost Authorisation Spend Report CAPITAL]
ORDER BY [Cost Authorisation Spend Report CAPITAL].[3 Digit Category];
For reference I have posted the very first query in the line of subsequent queries. This query control box values on my form to filter results
Would it make more sense to remove the parameters from the first query and put them in the final query?
SELECT [Cost Authorisation Table].Status, [Cost Authorisation Table].[Status 2], [Cost Authorisation Table].[Status 3], [Cost Authorisation Table].[Full Description], [Cost Authorisation Table].Date, [PREFIX] & [PO Numnber] AS [PO Number], [Cost Authorisation Table].[Budget Year], Right([Date],4) AS [Year], Right([Date],7) AS [Pre Month], Left([Pre Month],2) AS [Month], [Cost Authorisation Table].Building, [Consolidation of Category_USD_Cost Centre].Category, [Cost Authorisation Table].[Consolidated Code], [Cost Authorisation Table].[3 Digit Category], Null AS [Number Category], [Cost Authorisation Table].[PSLR Number], [Cost Authorisation Table].[Invoice Approved Date], Right([Invoice Approved Date],4) AS [Invoice Approved Year], [Labour Cost Table].Description, Right([Description],3) AS [Right Description], 0 AS Budget, [Cost Authorisation Table].[Total Cost], IIf([Month]=1 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Jan Actuals], IIf([Month]=2 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Feb Actuals], IIf([Month]=3 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Mar Actuals], IIf([Month]=4 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Apr Actuals], IIf([Month]=5 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [May Actuals], IIf([Month]=6 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Ju Actuals], IIf([Month]=7 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Jul Actuals], IIf([Month]=8 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Aug Actuals], IIf([Month]=9 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Sep Actuals], IIf([Month]=10 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Oct Actuals], IIf([Month]=11 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Nov Actuals], IIf([Month]=12 And ([Invoice Approved Year]=[Budget Year]),[Total Cost],0) AS [Dec Actuals], IIf([Invoice Approved Year]<>[Budget Year],[Total Cost],0) AS [Post Budget Year Actuals], 0 AS [Commitment to Spend], [Jan Actuals]+[Mar Actuals]+[Apr Actuals]+[May Actuals]+[Ju Actuals]+[Jul Actuals] AS [1st 6 Months Spend], [Jul Actuals]+[Aug Actuals]+[Sep Actuals]+[Oct Actuals]+[Nov Actuals]+[Dec Actuals] AS [2nd 6 Months Spend], [Commitment to Spend]+[1st 6 Months Spend]+[2nd 6 Months Spend]+[Post Budget Year Actuals] AS [Total Spend]
FROM ([Cost Authorisation Table] INNER JOIN [Consolidation of Category_USD_Cost Centre] ON [Cost Authorisation Table].[Consolidated Code] = [Consolidation of Category_USD_Cost Centre].Code) INNER JOIN [Labour Cost Table] ON [Cost Authorisation Table].[Cost Auth ID] = [Labour Cost Table].[Cost Authorisation ID]
WHERE ((([Cost Authorisation Table].[Budget Year])=[Forms]![Cost Authorisation Selection for Spend Report]![Year]) AND (([Cost Authorisation Table].Building)=[Forms]![Cost Authorisation Selection for Spend Report]![Search Criteria]) AND (([Cost Authorisation Table].[3 Digit Category])="MMC") AND (([Cost Authorisation Table].[Invoice Approved Date]) Is Not Null) AND ((Right([Description],3))<>"OTH" And (Right([Description],3))<>"CAP"));
