-----------------------------------------------------
|   ModId   |   ModText     |   AdditionalCost      |
-----------------------------------------------------
|     58    |   RANCH       |   2000                |
-----------------------------------------------------
|     86    |   GUACAMOLE   |   500                 |
-----------------------------------------------------
|     52    |   CEBOLLA     |   0                   |
-----------------------------------------------------
|     87    |   QUESO       |   150                 |
-----------------------------------------------------

---------------------------------------------
|   OrderId     |   OrderDateTime           |
---------------------------------------------
|   20001       |   2017-06-01 00:00:00     |
---------------------------------------------
|   20001       |   2017-06-01 00:00:00     |
---------------------------------------------
|   15224       |   2017-06-02 00:00:00     |
---------------------------------------------
|   13501       |   2017-06-03 00:00:00     |
---------------------------------------------

---------------------------------------------------------------------
|   TransaccionID   |   Mod1ID      |   Mod2ID      |   Mod3ID      |
---------------------------------------------------------------------
|     1             |               |   86          |   87          |
---------------------------------------------------------------------
|     2             |   58          |               |               |
---------------------------------------------------------------------
|     3             |   87          |   52          |               |
---------------------------------------------------------------------
|     4             |               |   58          |               |
---------------------------------------------------------------------

-------------------------------------------------
|   Modificador |   Cantidad    |   Valor       |
-------------------------------------------------
|   RANCH       |   2           |   4000        |
-------------------------------------------------
|   GUACAMOLE   |   5           |   2500        |
-------------------------------------------------
|   CEBOLLA     |   7           |   0           |
-------------------------------------------------
|   QUESO       |   4           |   600         |
-------------------------------------------------

SELECT
    t.MenuModifierText AS [Modificador],
    COUNT(*) AS [Cantidad],
    (SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) AS ValorMod,
    (SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) * COUNT(*) AS [Valor]
FROM(
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod1ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod2ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod3ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod4ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
) AS t
GROUP BY t.MenuModifierID, t.MenuModifierText;

Select ModID, Count(*) From [Transacciones] GROUP BY ModID

For I = 1 To 20
        CurrentDb.Execute "INSERT INTO [TransaccionesTEMP](TransaccionID, ModID)" & _
            " SELECT ORDTRDS.OrderID, MENUMODS.MenuModifierID " & _
            " FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD " & _
            " ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS " & _
            " ON MENUMODS.MenuModifierID = ORDTRDS.Mod" & I & "ID " & _
            " WHERE ORDHRD.OrderDateTime BETWEEN #" & Format(Fecha1, "mm/dd/yy") & "# AND #" & Format(Fecha2, "mm/dd/yy") & "#"
    Next I

    'Aquí ya tendrías la tabla que te comenté al principio. Con ella ahora se pueden hacer consultas mas elegantes, ya sea por código o usando consultas
End Sub

Public Sub GenerarConsultaTransacciones(ByVal Fecha1 As Date, ByVal Fecha2 As Date)
    Dim ConsultaSQL As String

    ConsultaSQL = _
        " SELECT t.MenuModifierText AS [Modificador], COUNT(*) AS [Cantidad]," & _
        "(SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) AS ValorMod," & _
        "(SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) * COUNT(*) AS [Valor]" & _
        "FROM("

    For I = 1 To 20
        ConsultaSQL = ConsultaSQL & _
            " INSERT INTO [TransaccionesTEMP](TransaccionID, ModID)" & _
            " SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID " & _
            " FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD " & _
            " ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS " & _
            " ON MENUMODS.MenuModifierID = ORDTRDS.Mod" & I & "ID " & _
            " WHERE ORDHRD.OrderDateTime BETWEEN #" & Format(Fecha1, "mm/dd/yy") & "# AND #" & Format(Fecha2, "mm/dd/yy") & "#"
        If I < 20 Then ConsultaSQL = ConsultaSQL & " UNION ALL "
    Next I

    ConsultaSQL = ConsultaSQL & ") AS t GROUP BY t.MenuModifierID, t.MenuModifierText"

    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Consulta TRansacciones"
    On Error GoTo 0
    CurrentDb.CreateQueryDef "Consulta Transacciones", ConsultaSQL
End Sub