CODEWITHSUNDEEP
sqlms-accessms-access-2013
IgorM
IgorM

Reputation: 198

MS Access: DSUM to sum only rows with same date

I have the following query where week is a date field, market and team are text strings.

SELECT t1.Week, t1.Market, t1.Team, Sum(t1.Audits) AS CFS_Audits, Sum(t1.Incorrect) AS CFS_Incorrect
FROM t1
GROUP BY t1.Week, t1.Market, t1.Team;

My goal is to have one addditional field that uses DSUM to calculate all audits that occurred on a given week. The field should thus have a lower granularity than CFS_Audits which is broken down at Week-Market-Team level.
I tried the following code X: DSUM("Audits", "t1", "[Week]="&[Week]) which was suggested in the closest question I have found here but the resulting field is consistently NULL. I suspect this might be because in the given example the field was stored as a numeral and not as a date. Or maybe I am taking the wrong approach altogether. Tutorials online also did not help as in all cases I have found they offer examples of the criteria parameter of DSUMwith a hard-coded criteria whereas here I need a row-by-row evaluation.

Thank you

Upvotes: 0

Views: 703

Answers (1)

Erik A
Erik A

Reputation: 32632

Dates in Access SQL are pretty hard to work with

The easiest way is to avoid any typecasting, is to use a subquery instead of a domain aggregate.

SELECT t.Week, t.Market, t.Team, Sum(t.Audits) AS CFS_Audits, Sum(t.Incorrect) AS CFS_Incorrect, (SELECT Sum(s.Audits) FROM t1 s WHERE s.Week = t.Week) As X
FROM t1 t
GROUP BY t.Week, t.Market, t.Team;

Alternatively, you can use a date in your DSUM, but it should be surrounded by # octothorpes and either formatted YYYY-MM-DD or MM/DD/YYYY:

SELECT t.Week, t.Market, t.Team, Sum(t.Audits) AS CFS_Audits, Sum(t.Incorrect) AS CFS_Incorrect, DSUM("Audits", "t1", "[Week]=" & Format([Week], "\#yyyy-mm-dd\#") As X
FROM t1 t
GROUP BY t.Week, t.Market, t.Team;

Upvotes: 1

Related Questions