X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Multiple Fields Running Total, Access 2016    
post Sep 6 2019, 12:12 PM

Posts: 75
Joined: 9-March 17

Can someone guide me through a vba code or really fast method to create a table with multiple fields (21 in total) with a running total for each field with a criteria of month and year?
My table looks like this:
NotificationDate ReasonCode
1/2/2019 A1
1/31/2019 A2
1/31/2019 C1
1/31/2019 C1
2/1/2019 B2
2/19/2019 F1
2/28/2019 F1
2/28/2019 F1
3/2/2019 E1
3/4/2019 B2
4/9/2019 F1
4/9/2019 F1
4/10/2019 B1
4/29/2019 F2
4/30/2019 F1

I would like to see the running totals in a table for each of the reason code across from Year and Month.
What I tried was to create a pivot table and then use that to create a dsum for each field. I have hundreds of records and it is taking too long.

The result I would like to see is:
NotifyYear NotifyMonth A1 A2 B1 B2 C1 E1 F1 F2
2019 1 1 1 2
2019 2 1 3
2019 3 2 1
2019 4 1 6 1
Go to the top of the page
post Sep 6 2019, 12:22 PM

Access Wiki and Forums Moderator
Posts: 76,409
Joined: 19-June 07
From: SunnySandyEggo

Hi. I think starting out with a crosstab query is a good plan; but if using a DSum() query against it is running too slow, then maybe you'll have to use VBA.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Sep 6 2019, 12:37 PM

UA Admin
Posts: 35,893
Joined: 20-June 02
From: Newcastle, WA

You can easily produce the Crosstab to get counts by month and year for each reason code. Two queries can do that:

Create a query called ReasonYearMonth.
SELECT Year([NotificationDate]) AS NotifyYear, Month([NotificationDate]) AS NotifyMonth, tblReasonCodes.ReasonCode
FROM tblReasonCodes;

And then:
TRANSFORM Count(ReasonYearMonth.NotifyMonth) AS [Total Of NotifyMonth]
SELECT ReasonYearMonth.NotifyYear, ReasonYearMonth.NotifyMonth
FROM ReasonYearMonth GROUP BY ReasonYearMonth.NotifyYear, ReasonYearMonth.NotifyMonth
PIVOT ReasonYearMonth.ReasonCode;

from there, the next step is to create a running sum query. Unfortunately, you're finding that the DSUM takes a long time to process all of your records.

What I might try next would be to export the results of the crosstab query to a workbook in an Excel template that uses the data to refresh a presentation worksheet which has the calculation you require in formulas for the cells in that presentation worksheet.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Sep 6 2019, 12:42 PM

Posts: 75
Joined: 9-March 17

What do you think about this snip-it?
strSQL1 = "SELECT NotifyYear, NotifyMonthNo FROM 2qryNotifyDateReasonCodesTotalbyYearMonth Group BY NotifyYear, NotifyMonthNo ORDER BY NotifyYear, NotifyMonthNo"
Set rst1 = db.OpenRecordset(strSQL1, , dbReadOnly)
While rst1.EOF = False
strSQL2 = "SELECT * FROM 2qryNotifyDateReasonCodesTotalbyYearMonth WHERE NotifyMonthNo<=" & [NotifyMonthNo] & " And NotifyYear=" & [NotifyYear] & ""
Set rst2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
intCount = 0
While rst2.EOF = False
intCount = intCount + 1
rst2!A1 = intCount
rst2!A2 = intCount
rst2!A3 = intCount
Set rst2 = Nothing
Go to the top of the page
post Sep 6 2019, 01:25 PM

Posts: 75
Joined: 9-March 17

Thank you!
Any feedback on the coding?
Go to the top of the page
post Sep 6 2019, 01:39 PM

Posts: 1,475
Joined: 2-April 09
From: somewhere out there...

crosstab query is non-uipdateable query.

Never stop learning, because life never stops teaching.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th October 2019 - 10:18 PM