Sum Through Previous Month

[jeran042]

DSum Through Previous Month

Good afternoon all,

I have a query that has a spread of number broken down by month.
Picture $12,000, broken down by month, so $1k each month.

The current structure of the query is a department name, and each of the months.

In addition, there is a calculated field. A DSum where I add up all of the month -1. So in this instance, January - May, so $5k.

The problem is that this is manual, and I want to try a formula based approach.

Can someone point me in the right direction on how to write this formula?

Here is what I have so far:

SUM: Nz([JANUARY],0)+Nz([FEBRUARY],0)+Nz([MARCH],0)+Nz([APRIL],0)+Nz([MAY],0)

 

[theDBguy]

Hi. Where is the data coming from? What does it look like originally? Are you using a crosstab query? If so, are you not able to do the sum before you do the crosstab?

 

[jeran042]

The data starts life as an excel file, and then brought into Access. And it is untouched in MS access, so it has the same structure.

It is not a crosstab query,

And as I am working through this, the months are actual name, not dates. So not 1/1/2019, but "JANUARY"

Im thinking I have to concatenate this somehow. Maybe by finding the name of the current month, and subtracting 1?

I have attached an export of the query.

 

[June7]

Since Jun-Dec have no data, could just include all months in the expression.

This is non-normalized data structure. A UNION query can rearrange fields to normalize but you really need month numbers, not names. And then if database is multi-year, need year component to filter. There is no designer or wizard for UNION, must type or copy/paste into SQLView of query builder.

SELECT Cost_Center, Department, January AS Data, 1 AS MoNum, "January" AS MoName, 2019 AS Yr FROM qrySALARIES_061319
UNION SELECT Cost_Center, Department, February, 2, "February", 2019 FROM qrySALARIES_061319
...
UNION SELECT Cost_Center, Department, December, 12, "December", 2019 FROM qrySALARIES_061319;

Now use that query as source for subsequent queries to dynamically filter and aggregate data or to create records in normalized table.