Manipulate column expression/formulas in multiple Access 2016 Queries

Question

I'm used to excel/excel vba and have been tasked with using access for reports.

Every month I have a multitude of different queries that I need to manipulate essentially the same expression/formula. Adding a column(month of the year) to the total up fiscal year as time passes.

Example of simplified expressions for the month columns that grabs data pending the metric by the month number: Column 3 is M1: Sum(IIf([TableName].Month=1 And Forms!frmTest!cboMetric="x",[x],IIf([TableName].Month=1 And Forms!frmTest!cboMetric="y",[y],0))) Column 4 is M2: Sum(IIf([TableName].Month=2 And Forms!frmTest!cboMetric="x",[x],IIf([TableName].Month=2 And Forms!frmTest!cboMetric="y",[y],0))) Ect for the Months.

Fiscal year to date column expression FYTD= [M1] + [M2] , ect.

So each month of the fiscal year I need to add the next month, so = [M1] + [M2] + [M3], then the month after that =[M1] + [M2] + [M3] + [M4], and so on until the new fiscal year which I just need =[M1].

I didn't know if in vba or some other way if I could automate this or put a formula in a table entry and have that be the expression for the queries I need and then just change that one entry.

Yes, code can modify query objects but why not just build query with all 12 months calculated and add all 12. If data is multi-year, probably need to include year as filter criteria for the query. — June7

mazoula mazoula · Accepted Answer · 2021-08-10T09:18:36

here is one example. I like to use public functions to implement business rules as then you can use the business rules everywhere.

Private metric As String
'encapsulating form properties in synthetic public properties using public functions has lots of advantages but is not necessary
Public Function getMetric() As String
getMetric = IIf(IsNull(metric), "default", metric)
End Function
Public Function setMetric(value) As Boolean
metric = value
setMetric = True ' calling subroutines can be confusing so pointless return value to make a function
End Function

Public Function MonthSum(Month As Integer, metric As String) As Double
Dim X, Y As Double
X = DLookup("X", "Table3", "Month = " & Month) 'need to get values from other rows so we must resort to Dlookup or correlated sub queries
Y = DLookup("Y", "Table3", "Month = " & Month)
MonthSum = IIf(metric = "x", X, IIf(metric = "y", Y, 0))
End Function
Public Function FYTD(Month As Integer, metric As String) As Double
'todo tighten this loop
Select Case Month
Case 1
FYTD = MonthSum(1, metric)
Case 2
FYTD = MonthSum(1, metric) + MonthSum(2, metric)
Case 3
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric)
Case 4
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric)
Case 5
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric)
Case 6
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric)
Case 7
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric)
Case 8
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric) + MonthSum(8, metric)
Case 9
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric) + MonthSum(8, metric) + MonthSum(9, metric)
Case 10
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric) + MonthSum(8, metric) + MonthSum(9, metric) + MonthSum(10, metric)
Case 11
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric) + MonthSum(8, metric) + MonthSum(9, metric) + MonthSum(10, metric) + MonthSum(11, metric)
Case 12
FYTD = MonthSum(1, metric) + MonthSum(2, metric) + MonthSum(3, metric) + MonthSum(4, metric) + MonthSum(5, metric) + MonthSum(6, metric) + MonthSum(7, metric) + MonthSum(8, metric) + MonthSum(9, metric) + MonthSum(10, metric) + MonthSum(11, metric) + MonthSum(12, metric)
End Select
End Function

for this example I set metric in a combobox on a form:

Private Sub cboMetric_AfterUpdate()
setMetric (Me.cboMetric.value)
End Sub

I then use metric in a query:

When I select y in cboMetric and rerun the query I get:

Manipulate column expression/formulas in multiple Access 2016 Queries

1 Answers