DSUM with a Group By

[Dave D]

I have a "working" DSUM that populates a text box on a report. However, it is picking up duplicates and I need to sum only unique rows. I generated a query that will give me what I need, but I can't figure out how to re-write it into a DSUM, incorporating the Group By, so I can throw it into the text box's Control Source.



Any help is much appreciated.


This is the working DSUM (that sums duplicate rows):

=Nz(DSum("invValue","tblTrackingDelivery","cRecordId="+CStr([cRecordId])+" AND Month(vPayNLTDate) = 11"),0)

This is the query that will give me what I need:

SELECT tblTrackingDelivery.cRecordId, tblTrackingDelivery.invValue, Month([vPayNLTDate])=11 AS November
FROM tblTrackingDelivery
GROUP BY tblTrackingDelivery.cRecordId, tblTrackingDelivery.invValue, Month([vPayNLTDate])=11
HAVING (((Month([vPayNLTDate])=11)=True));

[June7]

Use the name of the query object in place of the table name. The query is already filtered by month so that criteria is not needed in the DSum. Why are you use CStr? If cRecordID is a number type field, this would not work.

=Nz(DSum("invValue","queryname", "cRecordId=" & CStr([cRecordId])),0)

[Dave D]

I'm trying to display monthly totals in 12 different text boxes; the DSUM example was for the month of November. I provided the query example to show how a query would get the same information for the same month. I was hoping someone could look at the DSUM and the query and tell me how to put the two together in another DSUM - again, this is assuming DSUM can use Group By.

As for why use CStr([cRecordId]), it's because you need it when you have additional WHERE clauses behind it. If I didn't have the + " AND Month(vPayNLTDate) = 11") tacked on, then I "probably" wouldn't need it. I say "probably" because Access sometimes requires it and sometimes doesn't.

[June7]

I've never used CStr() when domain aggregate has multiple criteria.

Ampersand (&) is the preferred concatenation operator. If cRecordID is a number type field, this should work.

=Nz(DSum("invValue", "tblTrackingDelivery", "cRecordId=" & [cRecordId] & " AND Month(vPayNLTDate) = 11"), 0)

If you want the data aggregated before the DSum() then need a query object that the DSum can reference as I show in previous post.

Don't you also need the year in the criteria?

[Dave D]

From your reply I take it DSUM doesn't support Group By...

Two new queries and 24 text boxes later (sum of monies in /out per month), I now have a working report.

I also went back and changed the "+" to an ampersand, left out the CStr() and it works as advertised.

Thank you for your help.

Since I found a suitable workaround (with your help) I'll mark this thread complete.

[June7]

Not sure what you mean by "doesn't support Group By" - domain aggregate functions must refer to table or query objects. Could be a Group By aggregate query.