DSum not working after December in Financial Year (1 Viewer)

sparkes84 · Apr 17, 2015

Hi everyone,

I have the following in a query:
Count: Val(DSum("Num","Google_export_1Cancer_Target_Prea","DatePart('m',[StudyEntryDate])<=" & [AMonth] & " And DatePart('yyyy',[StudyEntryDate])<=" & [AYear]))

Our Financial Year runs from April to March.

The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.

How do I rectify this?

Many thanks,
Sally

plog · Apr 17, 2015

You need to rework your criteria to account for your oddly defined year. Can you explain what you are actually trying to accomplish? I understand its some sort of sum, but for what timeframe--whats [AMonth] represent?

Also, how come you are using a DSUM for this? It's in a query, it would be best to use a totals query.

Gasman · Apr 17, 2015

Have you considered using a yyyymm format to compare?

That way it would be between 201404 and 201503.

Alternatively convert your Amonth and AYear into a date and compare directly with the dates in your table. 01/04/2014 & 31/03/2015.

HTH

sparkes84 · Apr 17, 2015

Here is the output. The count field shows how the value increments as it sums up all recruitment up to and including that month, but for Jan to March (the latter part of the UK financial year), the increments stop and it starts afresh.

AYear is the YYYY part of the month date
AMonth is the M part of the month date
MonthOrder is putting the financial year months in order (ie April to March)
Count is the cumulative sum (which stops working after December).

MonthYear AYear AMonth MonthOrder Targets Count
2014 4 2014 4 1 1900 101
2014 5 2014 5 2 1900 225
2014 6 2014 6 3 1900 371
2014 7 2014 7 4 1900 496
2014 8 2014 8 5 1900 620
2014 9 2014 9 6 1900 754
2014 10 2014 10 7 1900 899
2014 11 2014 11 8 1900 1047
2014 12 2014 12 9 1900 1190
2015 1 2015 1 10 1900 182
2015 2 2015 2 11 1900 345
2015 3 2015 3 12 1900 349

I hope the above helps explain the problem further?

Thanks,
Sally

Gasman · Apr 18, 2015

I tried it out on some data i created and got the same result as you.

I then used the yyyymm format for a field, so i could get ascending values and used a subquery as explained here and got the results I was expecting.

I had to create another query first as I put 5 into all records and then grouped by month, counting the number of records and their sum to test the figures were correct. I noticed that the DSUM version did not start afresh for your new year.

FWIW here is my code

Code:

SELECT QryDsumGroup.Expr1 AS YM, [AYear] & Right("00" & [AMonth],2) AS Expr2, (select sum(eq2.sumofdsumnum)  from qryDsumGroup as eq2 where  eq2.expr1 <= Qrydsumgroup.expr1) AS [Running Tots], QryDsumGroup.CountOfDsumNum, QryDsumGroup.SumOfDsumNum
FROM QryDsumGroup
GROUP BY QryDsumGroup.Expr1, QryDsumGroup.SumOfDsumNum, QryDsumGroup.AYear, QryDsumGroup.AMonth, QryDsumGroup.CountOfDsumNum, QryDsumGroup.SumOfDsumNum
ORDER BY QryDsumGroup.Expr1;

HTH

DSum not working after December in Financial Year (1 Viewer)

sparkes84

Registered User.

plog

Banishment Pending

Gasman

Enthusiastic Amateur

sparkes84

Registered User.

Gasman

Enthusiastic Amateur

Attachments

Similar threads

Users who are viewing this thread