Solved How to achieve Pivot (1 Viewer)

atzdgreat

Member
Local time
Today, 12:24
Joined
Sep 5, 2019
Messages
42
OBJECTIVE:
Period Taken is Feb 2024
CY MTD Column is a total amount for Feb 2024 Period
CY YTD Column is a total amount from Jan 2024 to Feb 2024 Period
PY MTD Column is a total amount for Feb 2023 Period
PY YTD Column is a total amount from Jan 2023 to Feb 2023 Period
COHN314 and COHN315 are a pivot result with total amt for Feb 2024 Period

Ledger AccountCY MTDCY YTDPY MTDPY YTDCOHN314COHN315
1000001​
157,289.00190,764.00103,486.001,012,420.00119,242.0038,047.00

TABLE:
Ledger AcctCost CenterAmountPeriod
1000001​
COHN31415,425.00
1-Jan-24​
1000001​
COHN3143,475.00
1-Jan-24​
1000001​
COHN3156,000.00
1-Jan-24​
1000001​
COHN3158,575.00
1-Jan-24​
1000001​
COHN31423,457.00
1-Feb-24​
1000001​
COHN31495,785.00
1-Feb-24​
1000001​
COHN31536,589.00
1-Feb-24​
1000001​
COHN3151,458.00
1-Feb-24​
1000001​
COHN314123,456.00
1-Jan-23​
1000001​
COHN3142,547.00
1-Jan-23​
1000001​
COHN315687,456.00
1-Jan-23​
1000001​
COHN31595,475.00
1-Jan-23​
1000001​
COHN31421,567.00
1-Feb-23​
1000001​
COHN3141,585.00
1-Feb-23​
1000001​
COHN31515,756.00
1-Feb-23​
1000001​
COHN31564,578.00
1-Feb-23​

CODE:

Code:
strSQL = "TRANSFORM IIF(SUM(t1.Amount) IS NULL, 0, SUM(t1.Amount)) AS TotalCost " & _
             "SELECT t1.LedgerAcct,
             "IIF(SUM(t2.CYMAmt) IS NULL, 0, SUM(t2.CYMAmt)) AS CYMAmt, IIF(SUM(t2.CYYTDAmt) IS NULL, 0, SUM(t2.CYYTDAmt)) AS CYYTDAmt, " & _
             "IIF(SUM(t2.PYMAmt) IS NULL, 0, SUM(t2.PYMAmt)) AS PYMAmt, IIF(SUM(t2.PYYTDAmt) IS NULL, 0, SUM(t2.PYYTDAmt)) AS PYYTDAmt FROM (([Main Table] t1 " & _
             "FROM ((("
            
    strSQL = strSQL & _
             "SELECT LedgerAcct, CostCenter, SUM(TotCost) AS CYMAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #02/01/24# AND #02/29/24# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tCYM "


    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS CYYTDAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #01/01/24# AND #02/29/24# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tCYYTD ON tCYM.LedgerAcct = tCYYTD.LedgerAcct AND tCYM.CostCenter = tCYYTD.CostCenter) "


    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS PYMAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #02/01/23# AND #02/28/23# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tPYM ON tCYM.LedgerAcct = tPYM.LedgerAcct AND tCYM.CostCenter = tPYM.CostCenter) "
    
    strSQL = strSQL & _
             "LEFT JOIN (SELECT LedgerAcct, CostCenter, SUM(TotCost) AS PYYTDAmt FROM [Main Table] " & _
             "WHERE Period BETWEEN #01/01/23# AND #02/28/23# AND CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY LedgerAcct, CostCenter) tPYYTD ON tCYM.LedgerAcct = tPYYTD.LedgerAcct AND tCYM.CostCenter = tPYYTD.CostCenter) t2 " & _
             "ON t1.LedgerAcct = t2.LedgerAcct AND t1.CostCenter = t2.CostCenter "
    
    strSQL = strSQL & _
             "WHERE t1.Period BETWEEN #02/01/24# AND #02/29/24# AND t1.CostCenter IN ('COHN314','COHN315') " & _
             "GROUP BY t1.LedgerAcct " & _
             "PIVOT t1.CostCenter"
 
SQL:
PARAMETERS
   parYear Long,
   parMonth Long
;
TRANSFORM
   SUM(Q.Amount) AS A
SELECT
   Q.LedgerAcct
FROM
   (
      SELECT
         LedgerAcct,
         Amount,
         "CYMTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, parMonth, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "CYYTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, 1, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "PYMTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear - 1, parMonth, 1)
            AND
         Period < DateSerial(parYear - 1, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         "PYYTD" AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear - 1, 1, 1)
            AND
         Period < DateSerial(parYear - 1, parMonth + 1, 1)
      UNION ALL
         SELECT
         LedgerAcct,
         Amount,
         CostCenter AS ColumnName
      FROM
         TableX
      WHERE
         Period >= DateSerial(parYear, parMonth, 1)
            AND
         Period < DateSerial(parYear, parMonth + 1, 1)
            AND
         CostCenter IN("COHN314", "COHN315")
   ) AS Q
GROUP BY
   Q.LedgerAcct
PIVOT
   Q.ColumnName IN("CYMTD", "CYYTD", "PYMTD", "PYYTD", "COHN314", "COHN315")

PIVOT just does some reshaping of rows into columns with some grouping and aggregation.
The necessary information such as the column name in conjunction with the value and row name must be created beforehand.

I have also added parameterization so that you can be flexible by entering the year and the month. The query can be used as a saved query, which is what I would recommend here.
 
Last edited:
you can also test Form1.
 

Attachments

Users who are viewing this thread

Back
Top Bottom