Dsum Help

[ScottD]

Hi all, new to Dsums in Access and not sure if it will do what I am looking
for it to do so here goes.
I have a table where I require a running total of "Monthly Usage".
The Fields used are
-Material
-Monthly Usage

Once I have a running total for the Monthly Usage working, I then intend to
perform a simple calculation off of it along the lines of finding the percent
of total usage that particular material equates to.
Here is what it looks like in Excel, I can't get it to work in Access.

Material Monthly Usage Running Total Percent of Total
100486 $ 232,169 $232,169
3.42%
67138 $ 147,163 $379,331 5.59%
99894 $ 142,121 $521,452 7.68%
83920 $ 118,068 $639,520 9.42%
574317 $ 64,588 $704,108
10.37%

A lil help would be awesome!

 

[Allen Browne]

This can be done with a DSum() expression but there are more efficient
solutions.

See:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD
The article illustrates how to get the Year-to-date figure (which is what I
presume you mean by a running total.)

To get the grand total, the expression would be:
DSum("[Monthly Usage]", "[Table1]", "Material = " & Nz([Material], 0))
or if Material is a Text field (not a Number field), use:
DSum("[Monthly Usage]", "[Table1]", "Material = """ & [Material] & """")

For help with forming the 3rd argument, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
The explanation is for DLookup(), but the 3rd argument is formed exactly the
same way for DSum().

 

[ScottD]

Hi Allen, thanks for the quick response but since I would consider myself a
noob at the DSum functionality in Access I guess I still need some
clarification.
I got the calculation to work in the query view but it does not perform the
running total portion of the calculation.
It looks like this...

Material Monthly Usage Running Total
67048 258088 258088
100486 232168 232168
99812 228399 228399
67138 147162 147162
99894 140843 140843
83920 118068 118068
50690 112730 112730
100689 82367 82367
51529 77495 77495
I've long since swallowed my pride on this one and just basically need a
dumbed down explanation of the "criteria" portion of this expression.
DSum («expr», «domain», «criteria»)
--
Thanks!
Scott

This can be done with a DSum() expression but there are more efficient
solutions.

See:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD
The article illustrates how to get the Year-to-date figure (which is what I
presume you mean by a running total.)

To get the grand total, the expression would be:
DSum("[Monthly Usage]", "[Table1]", "Material = " & Nz([Material], 0))
or if Material is a Text field (not a Number field), use:
DSum("[Monthly Usage]", "[Table1]", "Material = """ & [Material] & """")

For help with forming the 3rd argument, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
The explanation is for DLookup(), but the 3rd argument is formed exactly the
same way for DSum().

 

[Allen Browne]

I've long since swallowed my pride on this one and just basically need a
dumbed down explanation of the "criteria" portion of this expression.
DSum («expr», «domain», «criteria»)

The simplest explanation I can give you of the 3 arguments is this one:
http://allenbrowne.com/casu-07.html

It explains DLookup(). Follow that through. Once you understand it, DSum()
is exactly the same, except it sums the first field instead of just
returning the first match.