Sum Through Previous Month (1 Viewer)

jeran042

Registered User.
Local time
Today, 08:12
Joined
Jun 26, 2017
Messages
127
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:

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

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,359
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

Registered User.
Local time
Today, 08:12
Joined
Jun 26, 2017
Messages
127
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.
 

Attachments

  • qrySALARIES_061319.xlsx
    13.1 KB · Views: 67

June7

AWF VIP
Local time
Today, 07:12
Joined
Mar 9, 2014
Messages
5,425
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.
 
Last edited:

Users who are viewing this thread

Top Bottom