Translating Sumfis to Access Query without Excel's helper row

[Elwood07]

Hi,

So in Excel this is a simple issue with Sumifs, but I'm new to ACCESS and don't know how to accomplish this in a query.
I'm working on month-end reporting and I have a table with 13 columns - an account number and 12 months. Given the closing period (ie P06) I want Access to sum the rows from period 1 to period 6 (or whatever period given). In excel I would use a helper row and SumIfs based on months, but in Access I'm really not so sure, other than just using 11 nested IIF formulas.

So essentially what I have now is

IIf([TempVars]![ClosingMonth]="P06",[P01]+[P02]+[P03]+[P04]+[P05]+[P06],0)

which works fine in a query, but if I put in P12 as my close month for example, I would want to it summing across columns 2 thru 12, and etc.

This is what a row of the table looks like, and it's in this format because that's exactly how our accounting system exports it (except the headers are custom).



The reason I'm using Access for this is this is just a very small piece of a much larger budgeting and forecasting tool that I don't want to use Excel for. I'm just trying out this solution. I changed jobs and I miss SAP so much and I hate using Excel for budgeting as much as I love Excel.





Thanks in advance!

[Welshgasman]

That is not how access is meant to handle data.
In Access you would have
AccountNumber
Period
Value

Then a simple query would give you your totals, and you present that as a crosstab which is what you have now.

Otherwise, write your own function to do that.

[June7]

Agree, data is not a normalized relational database structure.

Normalize data then an aggregate query can summarize by month and if you want to pivot the months, that would be CROSSTAB query.

If P12 is the end period, you would sum fields P01 thru P12 which are columns 2 thru 13.

What is the accounting system? Who is responsible for that output structure? Can they change it?

There are things you can do with received data to manipulate it. Options:

1. redesign your tables and code a function that will "unpivot" received data to normalized structure and save it

2. keep data structure and use a UNION query to "unpivot" data and use that query same as would use normalized table (except could not edit data) to build aggregate queries

3. keep data structure and use 11 IIf() expressions or VBA custom function to add up appropriate fields.

[madpiet]

As much as you may dislike Excel, it's your best friend for this.
Data > Get Data>From Excel. Select the AccctNum column, then select Unpivot Other Columns and you'll end up with records like this (AcctNum, [Period], Amount)

I used to never ever use Excel, but it's MSFT's bread and butter app, so it gets all the love. PowerQuery is your friend!

Then when you're done, maybe create a linked table to the resulting table from the unpivot.

Here's the PowerQuery I clicked together to do the transformation (there are only a few columns in mine because I didn't want to do lots of data entry... but that doesn't matter because the "Unpivot other columns" step will take care of as many columns as you have. The PowerQuery will transform the table so that it's more like a normalized database table:

let
    Source = Excel.Workbook(File.Contents("C:\Users\piete\OneDrive\Documents\Unpivot Account Periods.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"AcctNum", type text}, {"P01", Int64.Type}, {"P02", type number}, {"P03", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"AcctNum"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Period"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","P","",Replacer.ReplaceText,{"Period"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Period", Int64.Type}})
in
    #"Changed Type1"

The only part you'd have to replace is the path to the file in the "Source=..." statement. Then just link to that from Access and if you add more records to the source (and maybe run a refresh (like F9?)), the query will re-run and then you can just import all the data into Access.

[jojowhite]

I would make a function and Call the function from the Query.
In a Module (VBA) add paste this code:

Public Function fnSumPeriod(ByVal AcctNum As String, ByVal strPeriod As String) As Double
    ' replace the constant YourTableName with your Tablename
    Const cTable As String = "YourTableName"
    Dim i As Integer, j As Integer
    Dim sExpr As String
    fnSumPeriod = 0
    j = Val(Mid$(strPeriod, 2))
    If j <> 6 And j <> 12 Then
        Exit Function
    End If
    For i = 1 To j
        sExpr = sExpr & "+Nz(P" & Format$(i, "00") & ",0)"
    Next
    sExpr = Mid$(sExpr, 2)
    Debug.Print sExpr
    fnSumPeriod = DLookup(sExpr, cTable, "AcctNum = '" & AcctNum & "'")
End Function

sample Query:

SELECT 
   YourTableName.AcctNum, 
   fnSumPeriod([AcctNum],[TempVars]![ClosingMonth]) AS PeriodTotal 
FROM YourTableName;

[madpiet]

I would fix the problem properly. Normalize. Future you will thank you for it. You'll be able to write simple queries to get answers and no use that kind of crazy. I've written that kind of thing before, and it's okay if it's a one-off, but don't build your solutions around something like that. If you unpivot the data and then import the unpivoted result, your querying will be a lot more flexible and simpler to write.

[Elwood07]

@madpiet I think you and the others are right and going back to the drawing board.

I've always loved Excel but others don't. I just wanted something with an easy interface for my Users that are going to be editing forecasts and budgets in the future.
But I'm going to take your advice and learn PowerQuery, which I've actually never had the pleasure to use.

Any thoughts on linking Excel with Access, so I can take advantage of the forms in Access?

Thanks everyone for your help and feedback.

[Elwood07]

Thanks All!

[madpiet]

Don't get me wrong, I don't like starting over, but sometimes you don't have any choice. Here's the payoff in my opinion - the queries later on will be infinitely easier to write and understand. I can see where jojowhite is coming from, but I feel like that's a short term solution to the problem. You'd be better served fixing it properly at the beginning. That's why I suggested using Excel. if you love Excel and have never used PowerQuery, I gotta ask "Where have you been??!" <g>
Once you have created the PowerQuery, the result of each PowerQuery will be loaded into a separate worksheet, which you can then link to/import into Access. The one thing that took a bit to get used to is that PowerQuery just re-runs the PowerQueries when you refresh, so you get *everything* each time. So if you're importing, you may want to include a dummy column in your excel sheet/powerquery with the date...

if you can post a *dummy* file like the picture you posted, I can slap together a PowerQuery to transpose it. (Super handy if you have a folder full of Excel files and you need to do the same to each one). And then dump that result into your database.